Re: easy task: concurrent select-updates

From: Nickolay <nitro(at)zhukcity(dot)ru>
To: Kevin McConnell <kevin(dot)mcconnell(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: easy task: concurrent select-updates
Date: 2009-09-04 04:41:43
Message-ID: 4AA09A87.1050201@zhukcity.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message 纪晓曦 2009-09-04 05:28:22 Moving avg using SQL
Previous Message Juan Backson 2009-09-04 04:36:37 N + 1 replication