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

i'm writing pl/pgsql stored procedure return set of records; each record contains fields of existing table (call retailer, has 2 fields: retailer_key , retailer_name). this, of course, works:

create function proc_find_retailers  (in p_store_key int)    returns setof retailer    $$ ...` 

now want update sp returns additional 2 fields 'end' of each returned record. can 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)    $$ ... 

in real world, retailer table has 50 fields (not 2 in example), enumerating fields in returns table clause tedious. there shortcut this, might such (i realize i'm making stuff here that's syntactically illegal, i'm doing give flavor of 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)    $$ ... 

you could return whole row composite type , add more:

create or replace function f_rowplus()   returns table (rec demo, add_int int, add_txt text) $func$ select d, 5, 'baz'::text demo d; $func$  language sql; 

but then, when use simple call:

select * f_rowplus(); 

you row table demo separate composite type. you'd have call:

select (rec).*,  add_int, add_txt f_rowplus(); 

to individual columns. parentheses required.

aside: still evaluates function once - while direct call evaluate once every column in return type:

select (f_rowplus()).*; 

details:

• how avoid multiple function evals (func()).* syntax in sql query?

postgres bit inconsistent here. if create function with

create or replace function f_row2()   returns table (rec demo) ... 

then silently converted individual columns (decomposed). not link original composite type remains. cannot reference declared output column rec @ all, since has been replaced columns of decomposed type. call result in error message:

select rec f_row2();

same here:

create or replace function f_row3(out rec demo)   returns setof demo ... 

however, add any more out columns, composite type preserved declared (not decomposed) , can:

select rec f_rowplus(); 

with first function.

i created sql fiddle demonstrating variants.

---

aside
when using function returning multiple columns in from list (as table function) , decomposing in select list this:

select (rec).* f_rowplus(); 

... function still evaluated once - while calling and decomposing in select list directly this:

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

... evaluate once every column in return type. details:

• how avoid multiple function evals (func()).* syntax in sql query?