Race Condition between SELECT and INSERT for multiple columns

Note: This is a question which is a follow up of this solution. You need to read the link to get context for this question. Also, this is for postgres v9.4

If we want to return multiple columns now instead of just 1 column, how can we achieve it?

Let’s take a table t:

 create table t(tag_id int, tag text unique);

Now this is what I want: whenever I call a method f_tag_id, I want it to return all the columns for the unique row if it exists in the table t else insert it and return all the columns.

So these are the things I tried for the f_insert_tag

Option1:

CREATE OR REPLACE FUNCTION f_insert_tag(tag_p_id int, _tag text)
RETURNS TABLE(_tag_p_id int, _tag_ text) 
AS
$func$
    BEGIN
        INSERT INTO t(tag_id,tag) VALUES (tag_p_id, _tag);
        return query Select * from t where t.tag_id = tag_p_id;
        EXCEPTION WHEN UNIQUE_VIOLATION THEN  -- catch exception, NULL is returned
    END
$func$ LANGUAGE plpgsql;

Option 2:

   CREATE OR REPLACE FUNCTION f_insert_tag(tag_p_id int, _tag text, out _tag_id int, out _tag_ text) 
AS
$func$
    BEGIN
        INSERT INTO t(tag_id,tag) VALUES (tag_p_id, _tag);
        Select t.tag_id, t.tag from t where t.tag_id = tag_p_id into _tag_id, _tag_;
        EXCEPTION WHEN UNIQUE_VIOLATION THEN  -- catch exception, NULL is returned
    END
$func$ LANGUAGE plpgsql;

Option 3:

CREATE OR REPLACE FUNCTION f_insert_tag(tag_p_id int, _tag text)
returns setof t AS
$func$
  BEGIN
    INSERT INTO t(tag_id,tag) VALUES (tag_p_id, _tag);
    return query Select * from t where t.tag_id = tag_p_id;
    EXCEPTION WHEN UNIQUE_VIOLATION THEN  -- catch exception, NULL is returned
  END
$func$ LANGUAGE plpgsql;

All the 3 worked by themselves:

select f_insert_tag(1322, 'helloworldaa');
    f_insert_tag
---------------------
 (1322,helloworldaa)

For the other function, f_tag_id as well I tried many methods:

Option 1:

CREATE OR REPLACE FUNCTION f_tag_id(tag_p_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 f_insert_tag(tag_p_id, _tag)
      into _tag_id, _tag_;

      EXIT WHEN _tag_id IS NOT NULL;  -- else keep looping
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Option 2:

CREATE OR REPLACE FUNCTION f_tag_id(tag_p_id int, _tag text)
RETURNS table(_tag_id int, _tag_ text) AS
$func$

BEGIN
   LOOP
      SELECT t.tag_id, t.tag FROM t WHERE t.tag = _tag
      UNION  ALL
      SELECT f_insert_tag(tag_p_id, _tag)
      into _tag_id, _tag_;

      EXIT WHEN _tag_id IS NOT NULL;  -- else keep looping
   END LOOP;
END
$func$ LANGUAGE plpgsql;

For both these, I got the same error:

select f_tag_id(22, 'test');
ERROR:  each UNION query must have the same number of columns
LINE 3:       SELECT f_insert_tag(tag_p_id, _tag)
                     ^
QUERY:  SELECT t.tag_id, t.tag FROM t WHERE t.tag = _tag
      UNION  ALL
      SELECT f_insert_tag(tag_p_id, _tag)
CONTEXT:  PL/pgSQL function f_tag_id(integer,text) line 5 at SQL statement

Answer

The wrench in the works is SELECT f_insert_tag(tag_p_id, _tag) instead of

SELECT * FROM f_insert_tag(tag_p_id, _tag)

For Postgres 9.4

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

db<>fiddle here

For Postgres 9.5 or later:

CREATE 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, t.tag
   INTO   _tag_id_, _tag_;

   EXIT WHEN FOUND;
END LOOP;
END
$func$  LANGUAGE plpgsql;

db<>fiddle here

Basics here:

• Is SELECT or INSERT in a function prone to race conditions?