Thread: create type with %type or %rowtype
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
On 11/17/20 2:12 PM, Post Gresql wrote: > Hello. > > It seems that I can not create a type with > > create type my_type as my_table%rowtype; To me that is redundant as a table has a composite type already. > > or > > create type my_type as my_table.my_column%type; In plpgsql %type is effectively a placeholder, not sure how well that would work in a created 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@aklaver.com
(resending to include the list)
On Tue, Nov 17, 2020 at 3:12 PM Post Gresql <postgresql@taljaren.se> 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?
Or would it cause
too many problems?
If it doesn't have value it wouldn't matter whether it would be problematic.
David J.
On Tue, Nov 17, 2020 at 3:12 PM Post Gresql <postgresql@taljaren.se> 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
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@taljaren.se> wrote:create type my_type as my_table%rowtype;This would be redundant with existing behavior - all tables have a corresponding type alreadycreate 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.
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@taljaren.se> 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
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@taljaren.se
>> <mailto:postgresql@taljaren.se>> 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@taljaren.se
>> <mailto:postgresql@taljaren.se>> 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@aklaver.com
On Wed, Nov 18, 2020 at 12:34 AM Post Gresql <postgresql@taljaren.se> wrote:
or even a complete table row as return type.
As mentioned, this is already possible.
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.
The documentation for pl/pgsql says:
"By using %TYPE you don't need to know the data type of the structure you are referencing, and most importantly, if the data type of the referenced item changes in the future (for instance: you change the type of user_id from integer to real), you might not need to change your function definition."
In short, you cannot gain that benefit (avoid recompilation) at the SQL level. I believe your "change type" use case is thus rendered basically undoable. And I don't see the effort to be worth the benefit for "create type" alone.
Just write: create type my_type (a int, b int); -- b's type matches my_table.my_column
And/Or: comment on column my_type.b is '@depends on my_table.my_column%type'
David J.
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
"rettypeThe 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."
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
"rettypeThe 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.
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@aklaver.com
Hi, > On 18. Nov, 2020, at 22:08, Post Gresql <postgresql@taljaren.se> wrote: > > 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 referencethe type of a table column." right in the syntax: CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, ...] ) ] "RETURNS TABLE(...)" is probably what you're looking for? Cheers, Paul
On 11/18/20 10:04 PM, Paul Förster wrote: > Hi, > >> On 18. Nov, 2020, at 22:08, Post Gresql <postgresql@taljaren.se> wrote: >> >> 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 referencethe type of a table column." > > right in the syntax: > > CREATE [ OR REPLACE ] FUNCTION > name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr > ] [, ...] ] ) > [ RETURNS rettype > | RETURNS TABLE ( column_name column_type [, ...] ) ] > > "RETURNS TABLE(...)" is probably what you're looking for? That is a variation on the theme. The OP was looking for declaring a table%ROWTYPE in RETURNS rettype. You can do that by using the table composite type. Since RETURNS TABLE is essentially an alias for RETURNS SETOF you can use it to return a set of the table composite type. Though in RETURNS TABLE you can also 'create' your own table that has nothing to do with an existing table composite type. > > Cheers, > Paul > -- Adrian Klaver adrian.klaver@aklaver.com