Nickolay wrote:
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 =alse order by id loop
update msg set busy =rue 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 =rue where id = (select min(id) from msg where
busy =alse) returning *;
Cheers,
Kevin
Thank you guys! But what's min(id) for? Is it neccessary? Is there any
chance I can replace min(id) to LIMIT 1?
Best regards, Nick.
min(id) finds the smallest id in the table. We made the assumption that you wanted to get the messages out order by id from smallest to largest.
LIMIT 1 would be ok if you didnt care what order the messages were processed in.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
