clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805470 fiddles created (40818 in the last week).

CREATE TABLE tag (tag_id serial PRIMARY KEY, tag text UNIQUE); INSERT INTO tag(tag) VALUES ('foo');
1 rows affected
 hidden batch(es)


CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) LANGUAGE plpgsql AS $func$ BEGIN LOOP SELECT tag_id FROM tag WHERE tag = _tag INTO _tag_id; EXIT WHEN FOUND; INSERT INTO tag AS t (tag) VALUES (_tag) ON CONFLICT (tag) DO NOTHING RETURNING t.tag_id INTO _tag_id; EXIT WHEN FOUND; END LOOP; END $func$;
 hidden batch(es)


-- returns new tag_id SELECT f_tag_id('bar'); -- returns existing tag_id SELECT f_tag_id('foo'); SELECT f_tag_id(t) FROM (VALUES ('foo'), ('bar'), ('baz')) t(t);
f_tag_id
2
f_tag_id
1
f_tag_id
1
2
3
 hidden batch(es)


SELECT * FROM tag;
tag_id tag
1 foo
2 bar
3 baz
 hidden batch(es)


-- Postgres 9.4 or older CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT tag_id int) LANGUAGE plpgsql AS $func$ BEGIN LOOP BEGIN WITH sel AS (SELECT t.tag_id FROM tag t WHERE t.tag = _tag FOR SHARE) , ins AS (INSERT INTO tag(tag) SELECT _tag WHERE NOT EXISTS (SELECT 1 FROM sel) -- only if not found RETURNING tag.tag_id) -- qualified so no conflict with param SELECT sel.tag_id FROM sel UNION ALL SELECT ins.tag_id FROM ins INTO tag_id; EXCEPTION WHEN UNIQUE_VIOLATION THEN -- insert in concurrent session? RAISE NOTICE 'It actually happened!'; -- hardly ever happens END; EXIT WHEN tag_id IS NOT NULL; -- else keep looping END LOOP; END $func$;
 hidden batch(es)


-- init again DELETE FROM tag WHERE tag_id > 1;
2 rows affected
 hidden batch(es)


-- returns new tag_id SELECT f_tag_id('bar'); -- returns existing tag_id SELECT f_tag_id('foo'); SELECT f_tag_id(t) FROM (VALUES ('foo'), ('bar'), ('baz')) t(t);
f_tag_id
4
f_tag_id
1
f_tag_id
1
4
5
 hidden batch(es)


SELECT * FROM tag;
tag_id tag
1 foo
4 bar
5 baz
 hidden batch(es)