On 11/18/20 10:04 PM, Paul Förster wrote:
Hi,
On 18. Nov, 2020, at 22:08, Post Gresql <postgresql@xxxxxxxxxxx> wrote:
I might be stupid, but where in the document for create function does it say that the return type can be a table?
From the doc for version 13 https://www.postgresql.org/docs/13/sql-createfunction.html
"rettype
The return data type (optionally schema-qualified). The return type can be a base, composite, or domain type, or can reference the type of a table column."
right in the syntax:
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr
] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
"RETURNS TABLE(...)" is probably what you're looking for?
That is a variation on the theme. The OP was looking for declaring a
table%ROWTYPE in RETURNS rettype. You can do that by using the table
composite type. Since RETURNS TABLE is essentially an alias for RETURNS
SETOF you can use it to return a set of the table composite type. Though
in RETURNS TABLE you can also 'create' your own table that has nothing
to do with an existing table composite type.
Cheers,
Paul
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
