Re: create type with %type or %rowtype

On 11/18/20 1:08 PM, Post Gresql wrote:
> 
> On 2020-11-18 17:07, Adrian Klaver wrote:
>>
>> \d cell_per
>>                        Foreign table "public.cell_per"
>>   Column  |       Type        | Collation | Nullable | Default | FDW 
>> options
>> ----------+-------------------+-----------+----------+---------+------------- 
>>
>>  category | character varying |           |          |         |
>>  cell_per | integer           |           |          |         |
>> Server: test_db
>>
>> CREATE OR REPLACE FUNCTION public.type_test()
>>  RETURNS cell_per
>>  LANGUAGE plpgsql
>> AS $function$
>> DECLARE
>>     cp_type cell_per;
>> BEGIN
>>     SELECT INTO cp_type * from cell_per limit 1;
>>     RETURN cp_type;
>> END;
>> $function$
>>
>> select * from type_test();
>>   category  | cell_per
>> ------------+----------
>>  H PREM 3.5 |       18
>>
>> You can change the RETURNS to RETURNS SETOF and return multiple rows.
>>
> 
> I might be stupid, but where in the document for create function does it 
> say that the return type can be a table?

It doesn't but the above is not returning a table, it is returning a 
(composite)type.

And earlier in this thread, my comment:

"To me that is redundant as a table has a composite type already."

and from the %ROWTYPE portion of the plpgsql section:

https://www.postgresql.org/docs/12/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES

"(Since every table has an associated composite type of the same name, 
it actually does not matter in PostgreSQL whether you write %ROWTYPE or 
not. But the form with %ROWTYPE is more portable.)"


> 
>  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."
> 
> 




-- 
Adrian Klaver
adrian.klaver@aklaver.com