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