Thread: create type with %type or %rowtype

create type with %type or %rowtype

From
Post Gresql
Date:
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





Re: create type with %type or %rowtype

From
Adrian Klaver
Date:
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



Re: create type with %type or %rowtype

From
"David G. Johnston"
Date:
(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




Re: create type with %type or %rowtype

From
Post Gresql
Date:


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




Re: create type with %type or %rowtype

From
Adrian Klaver
Date:
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



Re: create type with %type or %rowtype

From
"David G. Johnston"
Date:
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.

Re: create type with %type or %rowtype

From
Post Gresql
Date:


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


Re: create type with %type or %rowtype

From
"David G. Johnston"
Date:
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

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

Re: create type with %type or %rowtype

From
Adrian Klaver
Date:
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



Re: create type with %type or %rowtype

From
Paul Förster
Date:
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


Re: create type with %type or %rowtype

From
Adrian Klaver
Date:
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