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

po 9. 8. 2021 v 21:41 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com> napsal:

> *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: *
>
> *https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter
> <https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter>*
>
> *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.*
>

This is true. There is no possibility to list source code with line
numbers, because anonymous blocks are not persistent. The most simple way
is creating simple function from your example

postgres=# \sf+ fx
CREATE OR REPLACE FUNCTION public.fx()
RETURNS void
LANGUAGE plpgsql
1 AS $function$
2 declare
3 r rect;
4 begin
5 select r1 -- line 5
6 into r
7 from t1
8 where k = 1; raise notice '%', r;
9 end;
10 $function$

postgres=# select fx();
ERROR: invalid input syntax for type integer: "(10,20)"
CONTEXT: PL/pgSQL function fx() line 5 at SQL statement

>
> *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?*
>
>
The problem is in implicit build of composite values. Postgres knows two
types - scalars and composites. The result of the query is always tuple,
and there are different rules when the target is composite or when the
target is scalar. Unfortunately, until execution the PLpgSQL engine has no
idea what type of expression will result. In your case, PLpgSQL got a
tuple, and try to make a composite value, because the target is a composite
value.

postgres=# do $body$
declare
r rect;
begin
select 10, 20
into r;
raise notice '%', r;
end;
$body$
;

NOTICE: (10,20)
DO

But same mechanism break your example -

postgres=# do $body$
declare
r rect;
begin
select (10, 20)
into r;
raise notice '%', r;
end;
$body$
;
ERROR: invalid input syntax for type integer: "(10,20)"
CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement

when you replace rect type by record type, you can see result

postgres=# do $body$
declare
r record;
begin
select (10, 20)::rect
into r;
raise notice '%', r;
end;
$body$
;
NOTICE: ("(10,20)")
DO

The result has a nested rect type. The solution is easy - you can unpack
composite value, and assign it

postgres=# do $body$
declare
r record;
begin
select ((10, 20)::rect).*
into r;
raise notice '%', r;
end;
$body$
;
NOTICE: (10,20)
DO

or your example
postgres=# do $body$
declare
r rect;
begin
select (r1).* -- line 5
into r
from t1
where k = 1; raise notice '%', r;
end;
$body$;
NOTICE: (10,20)
DO

>
> *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?*
>
>
The compiler checks just SQL syntax, but doesn't check semantic
(identifiers). At compile time, the referred objects should not exist. So
there is not any information about query results at compile time. The
database objects have to exist before execution. There are advantages (and
disadvantages) of this design. PL/pgSQL should not use forward declarations
- and the relations between database objects and code are not too strong
(PLpgSQL is much more dynamic than PL/SQL). On the other hand, some errors
can be detected at runtime only. And because both sides are composite,
plpgsql tries to run dynamic IO conversions, and it fails.

Although PL/pgSQL looks like PL/SQL, it is an absolutely different
technology. PL/SQL is a classic compiler based environment with strong type
checking - and composite types have to be known at compile time. PL/pgSQL
is interpreted environment, much more similar to Python - and composite
types can be static, but most of composite types are dynamic - they are
created by any query execution, and assign of composite value to composite
variable is mostly dynamic - based on assign of any individual field
instead copy of structure's related memory. So you should not see PL/SQL in
PL/pgSQL. It is similar to C and Javascript - the syntax is similar - based
on {}, but any other is different.

Some errors like this, but not this can be detected by plpgsql_check
https://github.com/okbob/plpgsql_check - probably the heuristic for type
check is not complete.

I am afraid that what you described cannot be fixed without a compatibility
break now.

Regards

Pavel