From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type |
Date: | 2021-08-09 19:41:33 |
Message-ID: | 4FC8BC35-0048-406D-9BFD-774D7963D31F@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Matthias Apitz | 2021-08-09 20:15:04 | (13.1) pg_basebackups ./. pg_verifybackup |
Previous Message | Mladen Gogala | 2021-08-09 16:23:40 | Re: JWT decoder |