Andy Colson wrote:
Kevin McConnell wrote:
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 *;
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();
Thanks a lot for your solution! It works great for now.
Here is the thing I did following your advice:
CREATE TYPE queued_msg_row AS
(id bigint
,sender character varying
,"text" text
...
,msg_type integer);
CREATE OR REPLACE FUNCTION public.get_queued_msg
(_route_id integer
,_channel_id integer)
RETURNS queued_msg_row LANGUAGE plpgsql
AS $function$
declare
rec queued_msg_row;
begin
for rec in SELECT id,sender,"text", ... , msg_type
FROM msg_queue WHERE busy=false AND route_id=_route_id
ORDER BY priority DESC, date_time ASC LIMIT 10 loop
UPDATE msg_queue SET busy=true, channel_id=_channel_id WHERE id =
rec.id AND busy=false;
if found then
return rec;
end if;
end loop;
return NULL;
end;
$function$
The only problem that remains is that this function returns an empty row
when it should return NULL (no row), but that's not a critical issue.
Best regards, Nick.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
