Re: Need to select and update with the same sql statement

On Wed, 13 Nov 2002, David Busby wrote:

> List,
> I need to do a command like:
>
> select * from "table" where "id"=54; update "table" set "col"=value
> where "id"=just selected id
>
> Is that possible? How would I work that into a StoredProcedure? I'm
> getting a race condition where two+ clients are asking for data but getting
> the same record, (each record is a datachunk for a distributed client).
> This results in each client working on the same data, not good. Any ideas?
> I'm posting to the php/sql list cause the clients ask for the datachunk via
> SOAP request that is processed via PHP. Any assistance would be great

It's time for transactions!

You should be able to do this in a transaction:

(pg_exec the SQL code here)
begin;
select * from table where "id"=54;
(assign the id to a var $id here)
update "table" set "col"=$value where "id"=$id;
end;

This should happen in such a way that other users can't see what's
happening until it's done.