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 $$ ...
You could return a whole row as composite type and add some more:
But then, when you use the simple call:
You get the row from table
demo
as separate composite type. You'd have to call:to get all individual columns. Parentheses required.
Postgres is a bit inconsistent here. If you create a function with:
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 columnrec
at all, since that has been replaced with the columns of the decomposed type. This call would result in an error message:Same here:
However, as soon as you add any more
OUT
columns, the composite type is preserved as declared (not decomposed) and you can: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 theSELECT
list like this:... the function is still evaluated once only - while calling and decomposing in the
SELECT
list directly like this:... would evaluate once for every column in the return type. See:
In Postgres 14 or later, you can also use standard-SQL syntax:
See: