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_insert_tag(_tag_id int, _tag text, OUT _tag_id_ int, OUT _tag_ text)
AS
$func$
BEGIN
INSERT INTO t(tag_id, tag)
VALUES (_tag_id, _tag)
RETURNING t.tag_id, t.tag
INTO _tag_id_, _tag_;
EXCEPTION WHEN UNIQUE_VIOLATION THEN
-- catch exception, return NULL
END
$func$ LANGUAGE plpgsql;
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
UNION ALL
SELECT * -- !!!
FROM f_insert_tag(_tag_id, _tag)
LIMIT 1
INTO _tag_id_, _tag_;
EXIT WHEN _tag_id_ IS NOT NULL; -- else keep looping
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 |