From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Howto build a funtion that selects an id or inserts a value
Date: Sat, 24 Sep 2011 02:06:16 +0200
Message-ID: <4E7D1EF8.7040304@gmx.net> (raw)
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;
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: Howto build a funtion that selects an id or inserts a value
In-Reply-To: <4E7D1EF8.7040304@gmx.net>
* 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