Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

>
>
>> I can get the reference by field name that I prefer with a schema-level
>> type:
>>
>> create type tmp as (b text, t type1);
>>
>> and by declaring "r" with this data type. But this is a greater
>> discomfort than using the dynamically shaped "record" because it needs you
>> to create a dedicated schema-level type for every new SELCT list that you
>> come need.
>>
>
> When It is possible I use a record type - some years ago, the work with
> this type was a little bit slower, but not now. The work with this type is
> little bit safer - because it gets real labels. Values with declared
> composite types uses positional assignment, that is not too safe.
>
> create table foo(a int, b int);
> create type footype as (a int, b int);
> insert into foo values(10, 200);
>
> postgres=# do $$
> declare f footype;
> r record;
> begin
> select b, a from foo into f;
> select b, a from foo into r;
> raise notice 'f.a: %, f.b: %', f.a, f.b;
> raise notice 'r.a: %, r.b: %', r.a, r.b;
> end;
> $$;
> NOTICE: f.a: 200, f.b: 10
> NOTICE: r.a: 10, r.b: 200
> DO
> postgres=# select * from foo;
> ┌────┬─────┐
> │ a │ b │
> ╞════╪═════╡
> │ 10 │ 200 │
> └────┴─────┘
> (1 row)
>
> But sometimes explicit type is necessary - when you want to return
> composite value and when you want to work with composite outside function,
> or when you want to serialize, or deserialize composite value to/from json.
>
> When you work with composite values, is good to enable warnings
>
>
> https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
>

static composite types can be good when you use dynamic SQL. The
plpgsql_check cannot derive output composite type from dynamic SQL. And it
can stop checking. When you use static composite type, then the check can
continue.

Regards

Pavel

> Regards
>
> Pavel
>