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

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;
1
  • 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, 2020 at 17:17
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:

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, 2020 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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