Message-ID: <4E7D1EF8.7040304@gmx.net>
Date: Sat, 24 Sep 2011 02:06:16 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Howto build a funtion that  selects an id or inserts a value
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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;