3

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?

0
2

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;
2
  • Don't forget to mark your own answer as correct - it might help others find a resolution to similar problems in the future!
    – Vérace
    Jun 9 '20 at 17:17
  • @Vérace "You can accept your own answer in 2 days" 😬
    – xehpuk
    Jun 9 '20 at 17:22
1

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) AS $$
#variable_conflict use_column 
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING id;
  END
$$ LANGUAGE plpgsql;  -- see below

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

Related:

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

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

1
  • The documentation uses SQL and C vs. plpgsql, pltcl, plperl and plpythonu. Interesting. 🤔 I generally don't use quotes if not needed, though.
    – xehpuk
    Jun 11 '20 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.