Re: INSERT OR UPDATE?

From: Jerry Sievers <jerry(at)jerrysievers(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: INSERT OR UPDATE?
Date: 2005-10-09 14:10:28
Message-ID: m34q7q233f.fsf@prod01.jerrysievers.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-10-09 15:37:05 Re: pg_autovacuum
Previous Message Jerry Sievers 2005-10-09 13:41:11 Re: INSERT OR UPDATE?