On Sun, Oct 09, 2005 at 10:10:28AM -0400, Jerry Sievers wrote: > [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
