create type with %type or %rowtype

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:

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.

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.

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

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

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.

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

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 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
adrian.klaver@aklaver.com