On Sun, Oct 09, 2005 at 10:10:28AM -0400, Jerry Sievers wrote:
> smorrey@gmail.com writes:
>
> > Hello all,
> >
> > I am writing an app in PHP that uses a PostGres database. One
> > thing i have noticed is that what should/could be a single line of
> > SQL code takes about 6 lines of PHP. This seem wasteful and
> > redundant to me.
>
> Here ya go!...
>
> create temp table foo (
> id int primary key,
> data text
> );
>
> create rule foo
> as on insert to foo
> where exists (
> select 1
> from foo
> where id = new.id
> )
> do instead
> update foo
> set data = new.data
> where id = new.id
> ;
This is very clever, but it has a race condition. What happens if
between the time of the EXISTS() check and the start of the UPDATE,
something happens to that row? Similarly, what if a row comes into
existence between the EXISTS() check and the INSERT?
The UPSERT example below, while a little more complicated to write and
use, handles this.
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
SQL:2003 standard MERGE should fix all this.
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!