2009/8/11 Peter Headland <pheadl...@actuate.com>: >> 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.
ctid is unique system column in every table. postgres=# create table x(a int); CREATE TABLE Time: 655,062 ms postgres=# insert into x values(10); INSERT 0 1 Time: 49,237 ms postgres=# insert into x values(10); INSERT 0 1 Time: 1,740 ms postgres=# select ctid, a from x; ctid | a -------+---- (0,1) | 10 (0,2) | 10 (2 rows) > > 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. why not? for small number of iteration is loop over cursor good solution. Pavel Stehule > > -- > 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 <da...@druid.net>: >> On Mon, 10 Aug 2009 17:52:36 -0700 >> "Peter Headland" <pheadl...@actuate.com> 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 <da...@druid.net> | 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
