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@taljaren.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
referencethe 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@aklaver.com