Re: INSERT OR UPDATE?

On Sun, Oct 09, 2005 at 10:10:28AM -0400, Jerry Sievers wrote:
> smorrey(at)gmail(dot)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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!