po 9. 8. 2021 v 21:41 odesílatel Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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: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 5With "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$
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
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$
;
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
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
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
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
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
