Re: create type with %type or %rowtype
On 11/18/20 10:04 PM, Paul Förster wrote:
Hi,
On 18. Nov, 2020, at 22:08, Post Gresql 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.kla...@aklaver.com
Re: create type with %type or %rowtype
Hi,
> On 18. Nov, 2020, at 22:08, Post Gresql 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
Re: create type with %type or %rowtype
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.kla...@aklaver.com
Re: create type with %type or %rowtype
On Wednesday, November 18, 2020, 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? > > 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
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
On Wed, Nov 18, 2020 at 12:34 AM Post Gresql 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
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 <mailto:postgre...@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 <mailto:postgre...@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.kla...@aklaver.com
Re: create type with %type or %rowtype
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 <mailto:postgre...@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 <mailto:postgre...@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
(resending to include the list) On Tue, Nov 17, 2020 at 3:12 PM Post Gresql 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 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
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.kla...@aklaver.com
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
