On 11/17/20 11:34 PM, Post Gresql wrote:
On 2020-11-18 04:37, David G. Johnston wrote:
(resending to include the list)
On Tue, Nov 17, 2020 at 3:12 PM Post Gresql <postgresql@xxxxxxxxxxx
<mailto:postgresql@xxxxxxxxxxx>> wrote:
create type my_type as my_table%rowtype;
This would be redundant with existing behavior - all tables have a
corresponding type already
create type my_type as my_table.my_column%type;
What does the indirection get us?
Correct? It seems to be a feature for plpgsql programing only, right?
Correct
But wouldn't that be a good thing to be able to do?
You are the one proposing it - why would it be a good thing to do?
My idea, that I did not explain properly, sorry for that, is that when I
write plpgsql functions I sometime need to have a certain column type as
return value, or even a complete table row as return type.
\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.
See also:
Polymorphic types explanation at bottom of this section:
https://www.postgresql.org/docs/12/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS
Using %TYPE with polymorphic types:
https://www.postgresql.org/docs/12/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE
Then it would be great if I could just refer to the column or row type
when delcaring the return type.
It would also be handy if I could reference types when declaring other
types,
for example
create type my_type (a int, b my_table.my_column%type);
The real reason: you will be sure you are using the same type
everywhere. And it is easier to change type later on, then only one
column has to be changed, not many and in a lot of different places.
I hope that explains my idea.
David J.
On Tue, Nov 17, 2020 at 3:12 PM Post Gresql <postgresql@xxxxxxxxxxx
<mailto:postgresql@xxxxxxxxxxx>> wrote:
Hello.
It seems that I can not create a type with
create type my_type as my_table%rowtype;
or
create type my_type as my_table.my_column%type;
Correct? It seems to be a feature for plpgsql programing only, right?
But wouldn't that be a good thing to be able to do? Or would it cause
too many problems?
Best regards
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
