PostgreSQL Function Error – 42702 ‘Column Reference “id” Is Ambiguous’ for RETURNING of Table-Returning PL/pgSQL Function

errorsfunctionsplpgsqlpostgresqlpostgresql-10

This works:

CREATE OR REPLACE FUNCTION sql_fun()
RETURNS TABLE (id UUID) AS $$
  INSERT INTO foo DEFAULT VALUES
  RETURNING id
$$ LANGUAGE SQL;

SELECT *
FROM sql_fun();

This doesn't:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING id;
  END
$$ LANGUAGE PLpgSQL;

SELECT *
FROM plpgsql_fun();

Is this a bug in PL/pgSQL? How can I fix this while keeping the return type as is?

Best Answer

The solution is to qualify all columns from RETURNING which have the same name as the columns from RETURNS TABLE with the name of the table which was INSERTED INTO:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING foo.id;
  END
$$ LANGUAGE PLpgSQL;

If the name of the table is long and there are multiple columns, the name can be aliased:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID, foo INT, bar INT) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO table_with_a_very_long_name AS x DEFAULT VALUES
    RETURNING x.id, x.foo, x.bar;
  END
$$ LANGUAGE PLpgSQL;

Related Solutions

PostgreSQL RETURN NEXT Function – How to Use

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
  c refcursor;
BEGIN
  c:='mycursorname';
  OPEN c FOR select * from generate_series(1,100000);
  return c;                                       
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;

 Output:

 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.

PostgreSQL PL/pgSQL – Quoting Table Named Like Keyword in PL/pgSQL

Explanation

I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution.

The reason why overlay is quoted is not because it's a function name, but because it's a reserved word.
Also, overlay() is not a "PL/pgSQL function" (nor PL/pgSQL keyword), it's a standard SQL function built into the Postgres core (LANGUAGE internal, so C behind the curtains). In fact, PL/pgSQL has nothing to do with any of this.

A simple logic error.

format('%I_new', old_name)

This would escape any non-standard identifier with surrounding double quotes before '_new' is appended and would fail even with text as input type. You have to append '_new' before quoting the whole of it:

format('%I', old_name || '_new')

But that still won't work for regclass. Keep reading.

It's nonsense to add quotes to a regclass variable with %I, since its text representation is already quoted automatically where needed. (Similar to quote_ident(), but cannot be NULL; since the input is regclass it cannot be NULL to begin with.) You would apply it twice. Always use %s for regclass input (the string as is).

format() is overkill for this. Just use quote_ident():

quote_ident(old_name || '_new')

Most importantly, as mentioned above, the text representation of a regclass variable is automatically escaped. That's built into the cast. To get to the raw text, retrieve it from system catalog pg_class directly. A regclass value is just the oid of this table internally.

SELECT relname FROM pg_class WHERE oid = $1;

Solution

This does what you are looking for:

CREATE OR REPLACE FUNCTION make_name(old_name regclass)
  RETURNS text AS
$func$
SELECT quote_ident(relname || '_new') FROM pg_class WHERE oid = $1;
$func$  LANGUAGE sql STABLE;

Test

CREATE TEMP TABLE "sUICiDAL' namE"();
CREATE TEMP TABLE overlay();

SELECT make_name('overlay') AS n1
     , make_name('"sUICiDAL'' namE"')  AS n2;

     n1      |          n2
-------------+----------------------
 overlay_new | "sUICiDAL' namE_new"

Note that overlay_new is a perfectly legal identifier so it's not quoted.

If you want the name unescaped (no double quotes) just use:

SELECT relname || '_new' FROM pg_class WHERE oid = $1;