Howto build a funtion that selects an id or inserts a value

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Howto build a funtion that selects an id or inserts a value
Date: 2011-09-24 00:06:16
Message-ID: 4E7D1EF8.7040304@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

I'd like to have a function that looks up an id of an item.
In case the item doesn't exist in the table yet it should be inserted
and the new id should be returned.

From the PG docu, I took the merge_db sample and modified it a bit.
This works but I'm wondering if INSERT part could be tuned.
Could I have something like
i := INSERT INTO _log.computer ( item ) VALUES ( data ) returning id;
so I dont have to query the sequence and create another roundtrip on the
network?

CREATE or replace FUNCTION find_or_insert_item ( data TEXT ) RETURNS
integer AS
$$
declare
i integer;
BEGIN
LOOP
-- first try to select the id of an item
select id into i from items where item ilike data;
IF found THEN
RETURN i;
END IF;
-- not there, so try to insert the item and retrieve the new id.
-- if someone else inserts the same item concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO items ( item ) VALUES ( data );
select currval('items_id_seq') into i;
RETURN i;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the SELECT again.
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

Browse pgsql-sql by date

  From Date Subject
Next Message Andreas 2011-09-24 09:38:48 Better way to check more than 1 value NOT IN (...)
Previous Message Filip Rembiałkowski 2011-09-23 19:18:09 Re: a spatial table's bounding box