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
