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/