Wampler, Steve wrote:
> ...
> I've got a database that (logically) represents a set of
> items, where each item has a primary key represented by two
> columns (id and name). If that key isn't in the database,
> I want to insert the item. If that key is in the database, I want
> to update the item. I have no need to retain the previous
> values.
>
You can solve your problem with a trigger and a view, that has the same attributes as
your table.
You insert the data into the view, not the table. The trigger first looks, if a row
with that primary key is allready in the table and decides,
if to use update or insert into the table.
example:
create table test_table (
id int4 primary key,
data text);
create view test_view as
select id, data
from test_table;
create function insert_or_update() returns opaque as '
declare
lid int4;
begin
select t.id into lid
from test_table t
where t.id = new.id;
if found then
update test_table
set data = new.data
where id = new.id;
else
insert into test_table
values (new.id, new.data);
end if;
return null;
end;
' language 'plpgsql';
create trigger insert_or_update_trigger
before insert on test_view
for each row execute procedure insert_or_update();
Of course this solution has some drawbacks.
PostgreSQL always returns INSERT 0 0, because the insert into the view is canceled by
returning null in the trigger.
But it works.
Test it with two inserts, that do insert
insert into test_view values (1,'one');
insert into test_view values (2,'two');
select * from test_view;
Now an insert that dose an update:
insert into test_view values (1,'ONE');
select * from test_view;
Gerhard
