From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Post Gresql <postgresql(at)taljaren(dot)se>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: create type with %type or %rowtype |
Date: | 2020-11-18 16:07:52 |
Message-ID: | 75e98bba-afa1-5c7a-f6a1-434fac52e4a0@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)taljaren(dot)se
>> <mailto:postgresql(at)taljaren(dot)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(at)taljaren(dot)se
>> <mailto:postgresql(at)taljaren(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2020-11-18 16:08:27 | Re: create type with %type or %rowtype |
Previous Message | Stephen Haddock | 2020-11-18 16:05:31 | psql backward compatibility |