From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Post Gresql <postgresql(at)taljaren(dot)se>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: create type with %type or %rowtype |
Date: | 2020-11-19 00:40:24 |
Message-ID: | 9e599ad6-6ea9-1689-8c59-e816f3eef5cc@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2020-11-19 04:29:55 | Re: pg_upgrade from 12 to 13 failes with plpython2 |
Previous Message | David G. Johnston | 2020-11-18 22:20:56 | Re: create type with %type or %rowtype |