Re: create type with %type or %rowtype

On 11/18/20 10:04 PM, Paul Förster wrote:
> Hi,
>
>> On 18. Nov, 2020, at 22:08, Post Gresql <postgresql(at)taljaren(dot)se> 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(dot)klaver(at)aklaver(dot)com