Re: INSERT OR UPDATE?

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!