Postgres 13

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 tag (tag_id serial PRIMARY KEY, tag text UNIQUE);
INSERT INTO tag(tag) VALUES ('foo');

CREATE TABLE

INSERT 0 1

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$;

CREATE FUNCTION

-- 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

SELECT 1

f_tag_id

1

SELECT 1

f_tag_id

1

2

3

SELECT 3

SELECT * FROM tag;

tag_id
tag

1
foo

2
bar

3
baz

SELECT 3

-- 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$;

CREATE FUNCTION

-- init again
DELETE FROM tag WHERE tag_id > 1;

DELETE 2

-- 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

SELECT 1

f_tag_id

1

SELECT 1

f_tag_id

1

4

5

SELECT 3

SELECT * FROM tag;

tag_id
tag

1
foo

4
bar

5
baz

SELECT 3