Search Postgresql Archives

Re: INSERT OR UPDATE?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



smorrey@xxxxxxxxx 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/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux