Re: INSERT OR UPDATE?

smorrey(at)gmail(dot)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
;

copy foo from stdin using delimiters ',';
1,hello
2,hello
\.

select * from foo order by id;

insert into foo values (
1,'it works!'
);

select * from foo order by id;

Outout...

CREATE TABLE
CREATE RULE
id | data
----+-------
1 | hello
2 | hello
(2 rows)

INSERT 0 0
id | data
----+-----------
1 | it works!
2 | hello
(2 rows)

HTH

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