Antw: Insert with replace?

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
inthe 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_tablet     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