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

> pavel(dot)stehule(at)gmail(dot)com wrote:
>
> bryn(at)yugabyte(dot)com <mailto:bryn(at)yugabyte(dot)com> wrote:
>
>> pavel(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>>> tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>
>>>> pavel(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)com> wrote:
>>>>
>>>> Some errors like this, but not this can be detected by plpgsql_check
>>>> https://github.com/okbob/plpgsql_check <https://www.google.com/url?q=https://github.com/okbob/plpgsql_check&source=gmail-imap&ust=1629227921000000&usg=AOvVaw3Et9tiGoSScn4bG0DPyF8J>
>>>> probably the heuristic for type check is not complete.
>>>
>>> STRICTMULTIASSIGNMENT would detect most cases of this, except that the condition is checked too late. We'd need to count the fields *before* trying to assign values, not after.
>>>
>>> In the meantime, it does seem like the docs could be more explicit about this, and perhaps give an example showing the (x).* solution.
>>
>> Yes, a more detailed explanation of this behavior can be nice. There can be an example of value unnesting, but I think so for this case, there should be mainly an example of ANSI assign syntax.
>>
>> var := (SELECT x FROM ..)
>>
>> This syntax has advantages so is not amigonuous for this case, and explicit unnesting is not necessary (and it is not possible). Moreover, this is ANSI SQL syntax.
>
> Consider this example:
>
> create type type1 as (a1 int, a2 int);
> create table tab1(k int primary key, b boolean not null, t type1 not null);
> insert into tab1(k, b, t) values(1, true, (10, 20));
> select b::text, t::text from tab1 where k = 1;
>
> It seems to be perfectly plausible—and so it seems equally plausible that you'd want to do it using PL/pgSQL. Each of these two alternatives, inspired by the advice that I got in this thread, works and produces the expected output:
>
> Yes, this works. This syntax is not ambiguous.
>
> do $body$
> declare
> r record;
> begin
> select ((b, t)).*
> into r
> from tab1
> where k = 1;
> raise info 'Alt 1: % | %', r.f1::text, r.f2::text;
>
> r := (
> select (b, t)
> from tab1
> where k = 1);
> raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
> end;
> $body$;
>
> It feels a smidge uncomfortable because I'm forced to use reference by field position (f1, f2) rather than by field name (b, t). But reference by position is commonplace in PostgreSQL (for example, in the PREPARE statement). So I'spose that I have to learn to like it.
>
> postgres=# do $$
> declare r record;
> begin
> select 10 as a, 20 as b into r;
> raise notice '% %', r.a, r.b;
> end;
> $$;
> NOTICE: 10 20
> DO
>
> The composite value always has structure, and types, but sometimes it can lose labels. You can push labels by casting
>
> r := (select (b, t) -- this is dynamic composity value, but without labels - the scalar value doesn't hold label
>
> or
>
> r := (select (b, t)::type1 -- it is composite with labels again

Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is composite with labels again". I tried this:

create procedure p(i in int)
language plpgsql
as $body$
declare
r record;
begin
case i
when 1 then
select (b, t)::type1
into r
from tab1
where k = 1;

when 2 then
r := (
select (b, t)::type1
from tab1
where k = 1);

else null;
end case;
end;
$body$;

call p(3);
call p(2);
call p(1);

My idea with using a procedure and choosing which code path is followed at run-time is to distinguish between compile-time errors (there are none here) and run-time errors. Of course, "call p(3)" finishes with no error.

But both the other calls cause the same error:

42846: cannot cast type record to type1

But you say that this should work!