create type with %type or %rowtype

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

create type with %type or %rowtype

Post Gresql
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




Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

David G Johnston
In reply to this post by Post Gresql
(resending to include the list)

On Tue, Nov 17, 2020 at 3:12 PM Post Gresql <[hidden email]> 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 <[hidden email]> 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




Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

Post Gresql


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 <[hidden email]> 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.

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 <[hidden email]> 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




Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

Adrian Klaver-4
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 <[hidden email]
>> <mailto:[hidden email]>> 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 <[hidden email]
>> <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

David G Johnston
In reply to this post by Post Gresql
On Wed, Nov 18, 2020 at 12:34 AM Post Gresql <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

Post Gresql
In reply to this post by Adrian Klaver-4


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


Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

David G Johnston
On Wednesday, November 18, 2020, Post Gresql <[hidden email]> 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. 
Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

Adrian Klaver-4
In reply to this post by Post Gresql
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

Paul Förster
In reply to this post by Post Gresql
Hi,

> On 18. Nov, 2020, at 22:08, Post Gresql <[hidden email]> 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 reference the 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

Reply | Threaded
Open this post in threaded view
|

Re: create type with %type or %rowtype

Adrian Klaver-4
On 11/18/20 10:04 PM, Paul Förster wrote:

> Hi,
>
>> On 18. Nov, 2020, at 22:08, Post Gresql <[hidden email]> 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 reference the 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
[hidden email]