postgresqlplpgsqlpostgresql-9.2composite-types

Easy way to have return type be SETOF table plus additional fields?

I'm writing a PL/pgSQL stored procedure that will return a set of records; each record contains all the fields of an existing table (call it Retailer, which has two fields: retailer_key and retailer_name). This, of course, works:

CREATE FUNCTION proc_Find_retailers
 (IN p_Store_key INT)
   RETURNS SETOF Retailer
   AS $$ ...`

Now I want to update the sp so that it returns an additional two fields to the 'end' of each returned record. I can do something such as:

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS TABLE (
      retailer_key int,
      retailer_name varchar(50),
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...

In the real world, my Retailer table has 50 fields (not the two in my example), so enumerating all those fields in the RETURNS TABLE clause is tedious. Is there any shortcut to this, so that I might say something such as (I realize I'm making stuff up here that's syntactically illegal, but I'm doing it to give you the flavor of what I'm looking for):

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS (SETOF Store,
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...
Solution

  • You could return a whole row as composite type and add some more:

    CREATE OR REPLACE FUNCTION f_rowplus()
  RETURNS TABLE (rec demo, add_int int, add_txt text) AS
$func$
SELECT d, 5, 'baz'::text FROM demo d;
$func$  LANGUAGE sql;

    But then, when you use the simple call:

    SELECT * FROM f_rowplus();

    You get the row from table demo as separate composite type. You'd have to call:

    SELECT (rec).*,  add_int, add_txt FROM f_rowplus();

    to get all individual columns. Parentheses required.

    Postgres is a bit inconsistent here. If you create a function with:

    CREATE OR REPLACE FUNCTION f_row2()
  RETURNS TABLE (rec demo) AS
...

    then the composite type demo is silently converted into individual columns (decomposed). No link to the original composite type remains. You cannot reference the declared output column rec at all, since that has been replaced with the columns of the decomposed type. This call would result in an error message:

    SELECT rec FROM f_row2();  -- error!

    Same here:

    CREATE OR REPLACE FUNCTION f_row3(OUT rec demo)
  RETURNS SETOF demo AS
...

    However, as soon as you add any more OUT columns, the composite type is preserved as declared (not decomposed) and you can:

    SELECT rec FROM f_rowplus();

    with the first function.

    db<>fiddle here - demonstrating all variants
    Old sqlfiddle

    Asides

    When using a function returning multiple columns in the FROM list (as table function) and decomposing in the SELECT list like this:

    SELECT (rec).* FROM f_rowplus();

    ... the function is still evaluated once only - while calling and decomposing in the SELECT list directly like this:

    SELECT (f_rowplus()).*;  -- also: different result

    ... would evaluate once for every column in the return type. See:

    In Postgres 14 or later, you can also use standard-SQL syntax:

    CREATE OR REPLACE FUNCTION f_rowplus_std()
  RETURNS TABLE (rec demo, add_int int, add_txt text)
  LANGUAGE sql PARALLEL SAFE
BEGIN ATOMIC
SELECT d, 5, 'baz'::text FROM demo d;
END;

    See: