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