Kevin McConnell wrote:
CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql AS $function$ declare rec record; begin for rec in select id from msg where busy = false order by id loop update msg set busy = true where id = rec.id and busy = false; if found then return rec.id; end if; end loop; return -1; end; $function$I think you could also do something roughly similar in a statement by using a RETURNING clause on the update, such as: update msg set busy = true where id = (select min(id) from msg where busy = false) returning *; Cheers, Kevin
I had thought of that, but you'd need to add one thing, in the update ' and busy = false ', cuz two people may get the same id from the select min(id). update msg set busy = true where busy = false and id = (select min(id) from msg where busy = false) returning *; but then you'd have to fire it over-and-over until you actually got a row updated. Seemed easer to put the loop in function, then you can: select id from getmsg(); -Andy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
