Re: plpgsql - DECLARE - cannot to use %TYPE or %ROWTYPE for composite types

2015-10-19 9:52 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi,
>
> We cannot to declare variable with referenced type on other composite
> variable. This limit is probably artificial, because any composite type is
> any type too in PostgreSQL.
>
> The issue:
>
> referencing on composite variables doesn't work
>
> do $$ declare x int; y x%type; begin end; $$; -- ok
> do $$ declare x pg_class; y x%type; begin end; $$; -- invalid type name
> "x%type"
> do $$ declare x pg_class; y x%rowtype; begin end; $$; -- relation "x" does
> not exist
>
> The %ROWTYPE needs a record in pg_class. Probably we should not to change
> it. The change can bring a compatibility issues. So there are two
> possibilities:
>
> 1. %TYPE can be used for any kind of variables. This behave will be
> consistent with polymorphic parameters - we have "anyelement", and we have
> not "anyrow".
>
> 2. introduce new keyword - %RECTYPE .. it can work, but there will be gap
> between polymorphic parameters.
>
> Comments, notices?
>
>
Hi

I am sending patch that enables to use references to polymorphic parameters
of row types. Another functionality is possibility to get array or element
type of referenced variable. It removes some gaps when polymorphic
parameters are used.

create type test_composite_type as (x int, y int);
create or replace function test_simple(src anyelement)
returns anyelement as $$
declare dest src%type;
begin
dest := src;
return dest;
end;
$$ language plpgsql;
select test_simple(10);
test_simple
-------------
10
(1 row)

select test_simple('hoj'::text);
test_simple
-------------
hoj
(1 row)

select test_simple((10,20)::test_composite_type);
test_simple
-------------
(10,20)
(1 row)

create or replace function test_poly_element(x anyelement)
returns anyarray as $$
declare result x%arraytype;
begin
result := ARRAY[x];
raise notice '% %', pg_typeof(result), result;
return result;
end;
$$ language plpgsql;
select test_poly_element(1);
NOTICE: integer[] {1}
test_poly_element
-------------------
{1}
(1 row)

select test_poly_element('hoj'::text);
NOTICE: text[] {hoj}
test_poly_element
-------------------
{hoj}
(1 row)

select test_poly_element((10,20)::test_composite_type);
NOTICE: test_composite_type[] {"(10,20)"}
test_poly_element
-------------------
{"(10,20)"}
(1 row)

create or replace function test_poly_array(x anyarray)
returns anyelement as $$
declare result x%elementtype;
begin
result := x[1];
raise notice '% %', pg_typeof(result), result;
return result;
end;
$$ language plpgsql;
select test_poly_array(ARRAY[1]);
NOTICE: integer 1
test_poly_array
-----------------
1
(1 row)

select test_poly_array(ARRAY['hoj'::text]);
NOTICE: text hoj
test_poly_array
-----------------
hoj
(1 row)

select test_poly_array(ARRAY[(10,20)::test_composite_type]);
NOTICE: test_composite_type (10,20)
test_poly_array
-----------------
(10,20)
(1 row)

Regards

Pavel

> Regards
>
> Pavel
>
>
>