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

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