By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t(tag_id int, tag text unique);
CREATE OR REPLACE FUNCTION f_tag_id(_tag_id int, _tag text, OUT _tag_id_ int, OUT _tag_ text) AS
$func$
BEGIN
LOOP
SELECT t.tag_id, t.tag
FROM t
WHERE t.tag = _tag
INTO _tag_id_, _tag_;
EXIT WHEN FOUND;
INSERT INTO t (tag_id, tag)
VALUES (_tag_id, _tag)
ON CONFLICT (tag) DO NOTHING
RETURNING t.tag_id, tag
INTO _tag_id_, _tag_;
EXIT WHEN FOUND;
END LOOP;
END
$func$ LANGUAGE plpgsql;
SELECT * FROM f_tag_id(1, 'foo');
| _tag_id_ | _tag_ |
|---|---|
| 1 | foo |
SELECT * FROM f_tag_id(1, 'foo');
| _tag_id_ | _tag_ |
|---|---|
| 1 | foo |
TABLE t;
| tag_id | tag |
|---|---|
| 1 | foo |