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
newid. -- 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;