Обсуждение: Howto build a funtion that selects an id or inserts a value

Поиск
Список
Период
Сортировка

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

От
Andreas
Дата:
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;