From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Tom Lane PostgreSQL <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type |
Date: | 2021-08-10 20:30:17 |
Message-ID: | D821D312-C6CB-4AC5-A4B5-1B4AF6A79510@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 10-Aug-2021, at 13:12, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
>>
>> 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.
I simply cannot mange this list's "partial quoting and bottom posting convention". Forgive me.
The code that I tried looked obviously broken. But it's what you said would work.
Anyway, it seems to me that what I wrote originally still stands. I can use a schema level type or a record. Each approach has its pros and cons.
From | Date | Subject | |
---|---|---|---|
Next Message | ourdiaspora | 2021-08-11 10:27:55 | Re: php connection failure |
Previous Message | Pavel Stehule | 2021-08-10 20:12:58 | Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type |