Search code examples
postgresqlpolymorphismplpgsqldynamic-sql

How to return a table by rowtype in PL/pgSQL

I am trying to implement a function that returns a table with the same structure as an input table in the parameter, using PL/pgSQL (PostgreSQL 9.3). Basically, I want to update a table, and return a copy of the updated table with plpgsql. I searched around SO and found several related questions (e.g. Return dynamic table with unknown columns from PL/pgSQL function and Table name as a PostgreSQL function parameter), which lead to the following minimal test example:

CREATE OR REPLACE FUNCTION change_val(_lookup_tbl regclass)
RETURNS _lookup_tbl%rowtype AS --problem line
$func$
BEGIN
    RETURN QUERY EXECUTE format('UPDATE %s SET val = 2 RETURNING * ; ', _lookup_tbl);
END
$func$ LANGUAGE plpgsql;

But I can't get past giving the correct return type for TABLE or SETOF RECORD in the problem line. According to this answer:

SQL demands to know the return type at call time

But I think the return type (which I intend to borrow from the input table type) is known. Can some one help explain if it is possible to fix the signature of the above PL/pgSQL function?

Note, I need to parametrize the input table and return the update of that table. Alternatives are welcome.

Solution

  • What you have so far looks good. The missing ingredient: polymorphic types.

    CREATE OR REPLACE FUNCTION change_val(_tbl_type anyelement)
  RETURNS SETOF anyelement  -- problem solved
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'UPDATE %s SET val = 2 RETURNING *;'
     , pg_typeof(_tbl_type))
     );
END
$func$;

    Call (important):

    SELECT * FROM change_val(NULL::some_tbl);

    db<>fiddle here
    Old sqlfiddle

    See (last paragraph):