Re: INSERT OR UPDATE? - Mailing list pgsql-general

From Jerry Sievers
Subject Re: INSERT OR UPDATE?
Date
Msg-id m37jcm24g8.fsf@prod01.jerrysievers.com
Whole thread Raw
List pgsql-general
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 is a sample of what I'm talking about ($db is a PDO already
> defined and created).

Well, at least you're using a very good DB!

This is easy to solve in Postgres.

Study up on "the rule system" and you will find the solution.  A
BEFORE INSERT trigger could be used here as well.

create table foo (a int not mull primary key, b text);

create rule maybe_update as on insert to foo where exists (select 1
from foo where a = new.a) do instead update foo set b = new.b where a
= new.a;

Untested example above... but have done this sort of thing a lot.  Can
be difficult to grasp at first.

A before insert trigger would test if the record exists already and if
so, do an update inside the trigger function and return null else
return new and the outter query proceed doing the insert.

HTH


--
-------------------------------------------------------------------------------
Jerry Sievers   305 854-3001 (home)     WWW ECommerce Consultant
                305 321-1144 (mobile    http://www.JerrySievers.com/

pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: Oracle buys Innobase
Next
From: Jerry Sievers
Date:
Subject: Re: INSERT OR UPDATE?