Updating a specific number of rows in pl/pgsql - Mailing list pgsql-sql

<p dir="LTR"><span lang="en-us"><font face="Calibri">I</font></span><span lang="en-us"><font
face="Calibri">’</font></span><spanlang="en-us"><font face="Calibri">m working on a queuing application. As part of
t</font><fontface="Calibri">his</font> <font face="Calibri">I</font></span><span lang="en-us"><font
face="Calibri">’</font></span><spanlang="en-us"><font face="Calibri">m trying to write a</font></span><span
lang="en-us"><font face="Calibri">pl/pgsql</font></span><span lang="en-us"> <font face="Calibri">function that updates
aspec</font><font face="Calibri">ific number of rows in the most effic</font></span><span lang="en-us"><font
face="Calibri">ientway possible. Multiple queues are contained within a single table.</font></span><p dir="LTR"><span
lang="en-us"><fontface="Calibri">I</font> <font face="Calibri">can get the rows I want to update like
this:</font></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"><fontface="Courier New"> </font></span><span lang="en-us"> <font face="Courier New">SELECT
*</font></span><pdir="LTR"><span lang="en-us"><font face="Courier New"></font></span><span lang="en-us"> <font
face="CourierNew"></font></span><span lang="en-us"> <font face="Courier New"></font></span><span lang="en-us"> <font
face="CourierNew">FROM queue</font></span><p dir="LTR"><span lang="en-us"><font face="Courier New"></font></span><span
lang="en-us"> <fontface="Courier New"></font></span><span lang="en-us"> <font face="Courier New"></font></span><span
lang="en-us"><font face="Courier New">WHERE</font> <font face="Courier New">id = p_queue_id</font></span><p
dir="LTR"><spanlang="en-us"><font face="Courier New"></font></span><span lang="en-us"> <font face="Courier
New"></font></span><spanlang="en-us"> <font face="Courier New"></font></span><span lang="en-us"> <font face="Courier
New">ORDERBY</font> <font face="Courier New">rank</font></span><p dir="LTR"><span lang="en-us"><font face="Courier
New"></font></span><spanlang="en-us"> <font face="Courier New"></font></span><span lang="en-us"> <font face="Courier
New"></font></span><spanlang="en-us"> <font face="Courier New">LIMIT p</font><font face="Courier
New">_number_of_items</font></span><spanlang="en-us"><font face="Courier New">;</font></span><span
lang="en-us"></span><pdir="LTR"><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Of
course,there may</font> <font face="Calibri">not be</font></span><span lang="en-us"> <font face="Courier
New">p</font><fontface="Courier New">_number_of_items</font></span><span lang="en-us"><font face="Calibri"></font>
<fontface="Calibri">a</font><font face="Calibri">vailable</font> <font face="Calibri">in the queue.</font></span><span
lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">I want to update all the rows in the
cur</font></span><spanlang="en-us"><font face="Calibri">sor in the same way:</font></span><p dir="LTR"><span
lang="en-us"><fontface="Courier New">  UPDATE q</font><font face="Courier New">ueue SET assigned</font><font
face="CourierNew"> = TRUE;</font></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"><font face="Calibri">The</font></span><span lang="en-us"> <font
face="Calibri">“</font></span><spanlang="en-us"><font face="Calibri">obvious</font></span><span lang="en-us"><font
face="Calibri">”</font></span><spanlang="en-us"><font face="Calibri"></font></span><span lang="en-us"> <font
face="Calibri">solution</font></span><spanlang="en-us"><font face="Calibri"> is to</font></span><span lang="en-us">
<fontface="Calibri">get a</font></span><span lang="en-us"> <font face="Calibri">cursor</font></span><span
lang="en-us"><fontface="Calibri"></font> <font face="Calibri">on the</font></span><span lang="en-us"> <font
face="Calibri">query</font></span><spanlang="en-us"><font face="Calibri"> and attempt to MOVE through
th</font></span><spanlang="en-us"><font face="Calibri">at cursor</font></span><span lang="en-us"><font face="Calibri">
ina loop</font></span><span lang="en-us"><font face="Calibri">,</font></span><span lang="en-us"> <font
face="Calibri">usingthe row count from the SELECT to tell me when I am done</font></span><span lang="en-us"><font
face="Calibri">.</font><font face="Calibri">I can then use</font></span><span lang="en-us"><font face="Calibri">
UPDATE</font></span><spanlang="en-us"> <font face="Calibri">…</font></span><span lang="en-us"><font face="Calibri">
WHERECURRENT OF</font></span><span lang="en-us"> <font face="Calibri">…</font></span><span lang="en-us"><font
face="Calibri">to do the updates. This seems cumbersome</font></span><span lang="en-us"> <font face="Calibri">and
inefficient</font></span><spanlang="en-us"> <font face="Calibri">to me</font></span><span lang="en-us"><font
face="Calibri">.</font><font face="Calibri">Is there a better way?</font></span><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Ideally, I</font></span><span
lang="en-us"><fontface="Calibri">’</font></span><span lang="en-us"><font face="Calibri">d like to do something
like:</font></span><pdir="LTR"><span lang="en-us"><font face="Courier New"> </font></span><span lang="en-us"> <font
face="CourierNew">UPDATE</font> <font face="Courier New">(SELECT</font></span><span lang="en-us"> <font face="Courier
New">…</font></span><spanlang="en-us"><font face="Courier New"> )</font></span><span lang="en-us"> <font face="Courier
New">…</font></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">-- </font></span><p dir="LTR"><span lang="en-us"><font
face="Calibri">PeterHeadland</font></span><p dir="LTR"><span lang="en-us"><font
face="Calibri">Architect</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">Actuate
Corporation</font></span><pdir="LTR"><span lang="en-us"></span>