[SQL] Best way to simulate Booleans
I know, I know, PostgreSQL has Booleans that work very nicely. Unfortunately, I have to create a schema that will work on Oracle as well as PostgreSQL, by which I mean that a single set of Java/JDBC code has to work with both databases. I have an XML meta-schema that enables me to generate appropriate DDL; that handles all the INTEGER vs. NUMBER(m,n) stuff. But Oracle simply has no Booleans, so I will have to resort to some more or less ugly alternative. I am hoping that others here have had to deal with this and can suggest an approach that will be minimally loathsome. God I hate Oracle... -- Peter Headland Architect - e.Reports Actuate Corporation
[SQL] Updating a specific number of rows in pl/pgsql
I'm working on a queuing application. As part of this I'm trying to write a pl/pgsql function that updates a specific number of rows in the most efficient way possible. Multiple queues are contained within a single table. I can get the rows I want to update like this: SELECT * FROM queue WHERE id = p_queue_id ORDER BY rank LIMIT p_number_of_items; Of course, there may not be p_number_of_items available in the queue. I want to update all the rows in the cursor in the same way: UPDATE queue SET assigned = TRUE; The "obvious" solution is to get a cursor on the query and attempt to MOVE through that cursor in a loop, using the row count from the SELECT to tell me when I am done. I can then use UPDATE ... WHERE CURRENT OF ... to do the updates. This seems cumbersome and inefficient to me. Is there a better way? Ideally, I'd like to do something like: UPDATE (SELECT ... ) ... -- Peter Headland Architect Actuate Corporation
Re: [SQL] Updating a specific number of rows in pl/pgsql
>Assuming that there is a unique identifier on queue Alas, there is not. The PK is made up of 4 columns. -- Peter Headland Architect Actuate Corporation -Original Message- From: D'Arcy J.M. Cain [mailto:da...@druid.net] Sent: Tuesday, August 11, 2009 03:25 To: Peter Headland Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql On Mon, 10 Aug 2009 17:52:36 -0700 "Peter Headland" wrote: > I can get the rows I want to update like this: > > SELECT * >FROM queue >WHERE id = p_queue_id >ORDER BY rank >LIMIT p_number_of_items; > > Of course, there may not be p_number_of_items available in the queue. > > I want to update all the rows in the cursor in the same way: > > UPDATE queue SET assigned = TRUE; Assuming that there is a unique identifier on queue, let's call it queue_id, you should be able to do something like this: UPDATE queue SET assigned = TRUE WHERE queue_id IN (SELECT queue_id FROM queue WHERE id = p_queue_id ORDER BY rank LIMIT p_number_of_items); -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updating a specific number of rows in pl/pgsql
> there are one fast trick > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing Thanks - that's a very useful page! Unfortunately, there is no single column that provides a unique id, and I am reluctant to add one (for example, using a sequence and a new index) for performance reasons. Given that additional constraint, is my original plan using a loop to iterate over a cursor reasonable? I don't anticipate p_number_of_items being more than 20. -- Peter Headland Architect Actuate Corporation -Original Message- From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Tuesday, August 11, 2009 03:55 To: D'Arcy J.M. Cain Cc: Peter Headland; pgsql-sql@postgresql.org Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql 2009/8/11 D'Arcy J.M. Cain : > On Mon, 10 Aug 2009 17:52:36 -0700 > "Peter Headland" wrote: >> I can get the rows I want to update like this: >> >> SELECT * >> FROM queue >> WHERE id = p_queue_id >> ORDER BY rank >> LIMIT p_number_of_items; >> >> Of course, there may not be p_number_of_items available in the queue. >> >> I want to update all the rows in the cursor in the same way: >> >> UPDATE queue SET assigned = TRUE; > > Assuming that there is a unique identifier on queue, let's call it > queue_id, you should be able to do something like this: > > UPDATE queue SET assigned = TRUE > WHERE queue_id IN (SELECT queue_id > FROM queue > WHERE id = p_queue_id > ORDER BY rank > LIMIT p_number_of_items); > there are one fast trick http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing p.s. replace DELETE by UPDATE regards Pavel Stehule > -- > D'Arcy J.M. Cain | Democracy is three wolves > http://www.druid.net/darcy/ | and a sheep voting on > +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updating a specific number of rows in pl/pgsql
> Unfortunately, there is no single column that provides a unique id. Correction - I did not understand what ctid was, but now I do, so I will try your tip. -- Peter Headland Architect Actuate Corporation -Original Message- From: Peter Headland Sent: Tuesday, August 11, 2009 10:05 To: 'Pavel Stehule'; D'Arcy J.M. Cain Cc: pgsql-sql@postgresql.org Subject: RE: [SQL] Updating a specific number of rows in pl/pgsql > there are one fast trick > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing Thanks - that's a very useful page! Unfortunately, there is no single column that provides a unique id, and I am reluctant to add one (for example, using a sequence and a new index) for performance reasons. Given that additional constraint, is my original plan using a loop to iterate over a cursor reasonable? I don't anticipate p_number_of_items being more than 20. -- Peter Headland Architect Actuate Corporation -Original Message- From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Tuesday, August 11, 2009 03:55 To: D'Arcy J.M. Cain Cc: Peter Headland; pgsql-sql@postgresql.org Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql 2009/8/11 D'Arcy J.M. Cain : > On Mon, 10 Aug 2009 17:52:36 -0700 > "Peter Headland" wrote: >> I can get the rows I want to update like this: >> >> SELECT * >> FROM queue >> WHERE id = p_queue_id >> ORDER BY rank >> LIMIT p_number_of_items; >> >> Of course, there may not be p_number_of_items available in the queue. >> >> I want to update all the rows in the cursor in the same way: >> >> UPDATE queue SET assigned = TRUE; > > Assuming that there is a unique identifier on queue, let's call it > queue_id, you should be able to do something like this: > > UPDATE queue SET assigned = TRUE > WHERE queue_id IN (SELECT queue_id > FROM queue > WHERE id = p_queue_id > ORDER BY rank > LIMIT p_number_of_items); > there are one fast trick http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing p.s. replace DELETE by UPDATE regards Pavel Stehule > -- > D'Arcy J.M. Cain | Democracy is three wolves > http://www.druid.net/darcy/ | and a sheep voting on > +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
