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

>
> r := (select (b, t)::type1 -- it is composite with labels again
>
>
>
postgres=# do $$
declare r record;
begin
r := (select (10,20));
raise notice '%', to_json(r);
end;
$$;
NOTICE: {"f1":10,"f2":20}
DO
postgres=# do $$
declare r record;
begin
r := (select (10,20)::footype);
raise notice '%', to_json(r);
end;
$$;
NOTICE: {"a":10,"b":20}
DO

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

the message is maybe not too intuitive, these casts are broken - you try to
cast (boolean, type1) => type1

The cast can ignore some fields from right or can add nulls from right, but
it cannot skip fields from left.

> But you say that this should work!
>
>
>