Received: from eba-haus.de (pec-13.au1.f.uunet.de [149.228.241.13]) by hub.org (8.9.3/8.9.3) with SMTP id DAA04474 for ; Fri, 2 Jun 2000 03:10:28 -0400 (EDT) (envelope-from DieringG@eba-haus.de) Received: from kopo001 by eba-haus.de;Fri, 02 Jun 2000 07:02:39 GMT with Novell_GroupWise; Fri, 02 Jun 2000 09:02:39 +0200 Message-Id: X-Mailer: Novell GroupWise 5.5.2 Date: Fri, 02 Jun 2000 09:02:12 +0200 From: "Gerhard Dieringer" To: " Cc: " Subject: Antw: Insert with replace? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id DAC04691 X-Archive-Number: 200006/16 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