Search Postgresql Archives

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]

 



The problem that I report here seems to be known and seems, too, to astonish and annoy users. It's a bare "computer says No". It's hard to find anything of ultimate use with Google search (either constrained to the PG doc or unconstrained). Here's an example on stackoverflow: 


However, it does give the _clue_ to the workaround.

Here's an illustration of the issue, starting with what works fine. I tested in using PG 13.3.

create type rect as (h int, w int);
create table t1(k int primary key, r1 rect not null);
insert into t1(k, r1) values(1, (10, 20));

do $body$
declare
  r rect;
begin
  r := (
    select r1
    from t1
    where k = 1);
  raise info '%', r::text;
end;
$body$;

The "raise info" shows what you'd expect.

This re-write fails. It simply uses the approach that anybody who hasn't yet been bitten by this would expect to work.

do $body$
declare
  r rect;
begin
  select r1 -- line 5
  into r
  from t1
  where k = 1;
end;
$body$;

This is the error:

22P02: invalid input syntax for type integer: "(10,20)" ... at line 5

With "VERBOSITY" set to "verbose", there's not hint to tell you what the problem is and how to work around it.

Question 1.
-----------
Where, in the PL/pgSQL doc, does it state that "select col into var" when col is a user-defined type doesn't work—and where is the viable approach shown?


Question 2.
-----------
If I can easily re-write a failing approach by hand (once I know that I must) so that it works, why cannot the PL/pgSQL compiler do this under the covers?



[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