Howto build a funtion that selects an id or inserts a value
От | Andreas |
---|---|
Тема | Howto build a funtion that selects an id or inserts a value |
Дата | |
Msg-id | 4E7D1EF8.7040304@gmx.net обсуждение исходный текст |
Список | 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 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;
В списке pgsql-sql по дате отправления: