Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 10-Aug-2021, at 13:12, Pavel Stehule <pavel.stehule@xxxxxxxxx> 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.






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux