42702 'column reference "id" is ambiguous' for RETURNING of table-returning PL/pgSQL function

Question

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?

Answer 1

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;

Answer 2

How can I fix this while keeping the return type as is?

You can even fix it keeping the whole function as is, by adding the special configuration parameter #variable_conflict:

CREATE OR REPLACE FUNCTION plpgsql_fun()
  RETURNS TABLE (id uuid)
  LANGUAGE plpgsql AS  -- see below
$func$
#variable_conflict use_column  -- !! 
BEGIN
   RETURN QUERY
   INSERT INTO foo AS f DEFAULT VALUES
   RETURNING f.id;
END
$func$;

But it's good form to table-qualify columns names anyway, like you found yourself - if at all possible. There are corner cases, where it's not easily possible:

• Naming conflict between function parameter and result of JOIN with USING clause

Related:

• INSERT INTO … RETURNING - ambiguous column reference
• How to return result of a SELECT inside a function in PostgreSQL?

In early versions, Postgres did not raise an exception, but silently preferred parameter values over columns:

• Postgres function NULL value for row that references NEW

And avoid CaMeL-casing the language name plpgsql. That stops working when quoted, which (unfortunately) is a wide-spread malpractice.