pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gerhard Dieringer <DieringG@eba-haus.de>
To: " <Steve Wampler" <swampler@noao.edu>
Cc: " <postgres-sql" <pgsql-sql@postgresql.org>
Subject: Antw: Insert with replace?
Date: Fri, 02 Jun 2000 09:02:12 +0200
Message-ID: <s937782f.046@kopo001> (raw)


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





Message-ID: <s937782f.046@kopo001>
Permalink:  ../s937782f.046@kopo001/
Also on:    postgresql.org/message-id/s937782f.046@kopo001

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: DieringG@eba-haus.de, Steve Wampler" <swampler@noao.edu, postgres-sql" <pgsql-sql@postgresql.org
  Subject: Re: Insert with replace?
  In-Reply-To: <s937782f.046@kopo001>
  References: <s937782f.046@kopo001>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox