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

út 10. 8. 2021 v 21:25 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>>> 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.
>>
>
There are a lot of use cases for static composite types. Everywhere on the
interface.

http://okbob.blogspot.com/2013/10/using-custom-composite-types-in.html

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