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@xxxxxxxxxxx
