Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EC695633420 for ; Mon, 10 Aug 2009 22:07:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 65800-03 for ; Tue, 11 Aug 2009 01:07:36 +0000 (UTC) X-Greylist: delayed 00:15:15.489622 by SQLgrey-1.7.6 Received: from wyattearp.actuate.com (mx1.actuate.com [69.1.81.237]) by mail.postgresql.org (Postfix) with SMTP id B838B633219 for ; Mon, 10 Aug 2009 22:07:44 -0300 (ADT) Received: from exchange8.actuate.com (Not Verified[192.168.11.22]) by wyattearp.actuate.com with MailMarshal (v6, 1, 4, 441) id ; Mon, 10 Aug 2009 17:52:16 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CA1A1D.F9262712" Subject: Updating a specific number of rows in pl/pgsql Date: Mon, 10 Aug 2009 17:52:36 -0700 Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Updating a specific number of rows in pl/pgsql Thread-Index: AcoaHgTtODh7OfpsQMWwHycV5sYOEw== From: "Peter Headland" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.554 tagged_above=-10 required=5 tests=AWL=0.555, BAYES_05=-1.11, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/35 X-Sequence-Number: 32944 This is a multi-part message in MIME format. ------_=_NextPart_001_01CA1A1D.F9262712 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D 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 =3D 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 ... ) ... --=20 Peter Headland Architect Actuate Corporation ------_=_NextPart_001_01CA1A1D.F9262712 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D = 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 =3D 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

------_=_NextPart_001_01CA1A1D.F9262712--