Re: create type with %type or %rowtype - Mailing list pgsql-general

From David G. Johnston
Subject Re: create type with %type or %rowtype
Date
Msg-id CAKFQuwacrygrhpaf_32UYJA+9fK28DM1Y8icDje=tRrF5fFdmQ@mail.gmail.com
Whole thread Raw
In response to Re: create type with %type or %rowtype  (Post Gresql <postgresql@taljaren.se>)
List pgsql-general
On Wednesday, November 18, 2020, Post Gresql <postgresql@taljaren.se> 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?

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




The word “composite”.  Every table has an associated composite type of the same name.

David J. 

pgsql-general by date:

Previous
From: Devrim Gündüz
Date:
Subject: Re: pg_upgrade from 12 to 13 failes with plpython2
Next
From: Adrian Klaver
Date:
Subject: Re: create type with %type or %rowtype