PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
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?
On Mon, Aug 9, 2021 at 12:41 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
*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>**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 first paragraph of the SO answer completely explains why this occurs.
However, the following 2 locations explain how we get here
1) https://www.postgresql.org/docs/current/rowtypes.html
Opening sentence of that page.
"A *composite type* represents the structure of a row or record;"
2) https://www.postgresql.org/docs/current/plpgsql-statements.html -
Section 42.5.3
"The result of a SQL command yielding a single row (possibly of multiple
columns) can be assigned to a record variable, row-type variable, or list
of scalar variables."
You did not provide a scalar variable - you provided a composite type -
which equates to a record/row-type variable and therefore, as described,
the engine tried to place each column returned into a column of your
composite type. Therefore the first column of the select result is placed
in the first column of your composite type - and you get an error.
It would seem rather clear that a sentence discussing composite types is
very much an option here in 42.5.3 to clarify it further given your
confusion today.
*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?*
First, there is no need to not write the select almost the way you
initially tried. The following works just fine.
select (r1).h, (r1).w
into r
from t1
where k = 1;
The engine sees a composite type as the receiver and places the data in it
as it is supposed to.
As another example, switch out your do with the following and it works
fine. It's not user-defined types - but rather how they work.
create type rect_bucket as (r rect);
do $body$
declare
r rect_bucket;
begin
select r1
into r
from t1
where k = 1;
end;
$body$;
Since the composite type is a single column of rect type - the select into
works - as does the variant I showed earlier.
John
po 9. 8. 2021 v 21:41 odesílatel Bryn Llewellyn <bryn@yugabyte.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
Pavel Stehule <pavel.stehule@gmail.com> writes:
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.
STRICTMULTIASSIGNMENT would detect most cases of this, except that
the condition is checked too late. We'd need to count the fields
*before* trying to assign values, not after.
In the meantime, it does seem like the docs could be more explicit
about this, and perhaps give an example showing the (x).* solution.
regards, tom lane
tgl@sss.pgh.pa.us wrote:
pavel.stehule@gmail.com writes:
Some errors like this, but not this can be detected by plpgsql_check
https://www.google.com/url?q=https://github.com/okbob/plpgsql_check&source=gmail-imap&ust=1629148434000000&usg=AOvVaw3f9UAP7RvDPC2QKi3_4Mj0 - probably the heuristic for type
check is not complete.STRICTMULTIASSIGNMENT would detect most cases of this, except that the condition is checked too late. We'd need to count the fields
*before* trying to assign values, not after.In the meantime, it does seem like the docs could be more explicit about this, and perhaps give an example showing the (x).* solution.
Tom, Pavel, and John, thanks for your quick responses. I've filed them all away and I'm hoping that I won't be caught out by this in the future.
It now seems to me to be odd, in the light of the explanations for why the naïve (PL/SQL-style) syntax doesn't work in PL/pgSQL, that assigning a scalar subquery to a variable of the composite type in question _does_ work! But don't take that as a question. I'm going to regard this as "case closed".
po 9. 8. 2021 v 23:13 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
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.STRICTMULTIASSIGNMENT would detect most cases of this, except that
the condition is checked too late. We'd need to count the fields
*before* trying to assign values, not after.In the meantime, it does seem like the docs could be more explicit
about this, and perhaps give an example showing the (x).* solution.
Yes, a more detailed explanation of this behavior can be nice. There can
be an example of value unnesting, but I think so for this case, there
should be mainly an example of ANSI assign syntax.
var := (SELECT x FROM ..)
This syntax has advantages so is not amigonuous for this case, and explicit
unnesting is not necessary (and it is not possible). Moreover, this is ANSI
SQL syntax.
Regards
Pavel
Show quoted text
regards, tom lane
It now seems to me to be odd, in the light of the explanations for why the
naïve (PL/SQL-style) syntax doesn't work in PL/pgSQL, that assigning a
scalar subquery to a variable of the composite type in question _does_
work! But don't take that as a question. I'm going to regard this as "case
closed".
This depends on how integration of PL/pgSQL and SQL is designed. PL/pgSQL
is a relatively small procedural interpretation over SQL engine. When you
evaluate a query, then you always get a composite value (named tuple)
always (in all cases).
SELECT 10, 20 INTO rec;
In this case you get composite (10,20) and it can be assigned to composite
without problems.
SELECT (10,20) INTO rec
returns composite ((10,20)), and that cannot be assigned to your composite.
Syntax rec := (SELECT 10,20) is not possible. Subquery can return only one
value always. More values are not allowed.
rec := (SELECT (10,20)) is working, because you can assign (in all cases)
the first field of returned composite value. This syntax cannot be
ambiguous.
If you work intensively with plpgsql, then it can be a very informative
look at plpgsql source code. Don't be afraid it is not too long, and you
will see. It is very simple. Then you can understand how it works.
https://github.com/postgres/postgres/tree/master/src/pl/plpgsql/src
Regards
Pavel
pavel.stehule@gmail.com wrote:
tgl@sss.pgh.pa.us wrote:
pavel.stehule@gmail.com wrote:
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.STRICTMULTIASSIGNMENT would detect most cases of this, except that the condition is checked too late. We'd need to count the fields *before* trying to assign values, not after.
In the meantime, it does seem like the docs could be more explicit about this, and perhaps give an example showing the (x).* solution.
Yes, a more detailed explanation of this behavior can be nice. There can be an example of value unnesting, but I think so for this case, there should be mainly an example of ANSI assign syntax.
var := (SELECT x FROM ..)
This syntax has advantages so is not amigonuous for this case, and explicit unnesting is not necessary (and it is not possible). Moreover, this is ANSI SQL syntax.
Consider this example:
create type type1 as (a1 int, a2 int);
create table tab1(k int primary key, b boolean not null, t type1 not null);
insert into tab1(k, b, t) values(1, true, (10, 20));
select b::text, t::text from tab1 where k = 1;
It seems to be perfectly plausible—and so it seems equally plausible that you'd want to do it using PL/pgSQL. Each of these two alternatives, inspired by the advice that I got in this thread, works and produces the expected output:
do $body$
declare
r record;
begin
select ((b, t)).*
into r
from tab1
where k = 1;
raise info 'Alt 1: % | %', r.f1::text, r.f2::text;
r := (
select (b, t)
from tab1
where k = 1);
raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
end;
$body$;
It feels a smidge uncomfortable because I'm forced to use reference by field position (f1, f2) rather than by field name (b, t). But reference by position is commonplace in PostgreSQL (for example, in the PREPARE statement). So I'spose that I have to learn to like it.
Do you (all) think that the "Alt 1" and "Alt 2" that I show are the best way to implement the requirement? I prefer "Alt 2" because it doesn't have the clutter (and the burden for understanding and readability) of the extra parentheses and the ".*".
I can get the reference by field name that I prefer with a schema-level type:
create type tmp as (b text, t type1);
and by declaring "r" with this data type. But this is a greater discomfort than using the dynamically shaped "record" because it needs you to create a dedicated schema-level type for every new SELCT list that you come need.
út 10. 8. 2021 v 19:48 odesílatel Bryn Llewellyn <bryn@yugabyte.com> napsal:
pavel.stehule@gmail.com wrote:
tgl@sss.pgh.pa.us wrote:
*pavel.stehule@gmail.com <pavel.stehule@gmail.com> wrote:*
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.STRICTMULTIASSIGNMENT would detect most cases of this, except that the
condition is checked too late. We'd need to count the fields *before*
trying to assign values, not after.In the meantime, it does seem like the docs could be more explicit about
this, and perhaps give an example showing the (x).* solution.Yes, a more detailed explanation of this behavior can be nice. There can
be an example of value unnesting, but I think so for this case, there
should be mainly an example of ANSI assign syntax.var := (SELECT x FROM ..)
This syntax has advantages so is not amigonuous for this case, and
explicit unnesting is not necessary (and it is not possible). Moreover,
this is ANSI SQL syntax.Consider this example:
create type type1 as (a1 int, a2 int);
create table tab1(k int primary key, b boolean not null, t type1 not null);
insert into tab1(k, b, t) values(1, true, (10, 20));
select b::text, t::text from tab1 where k = 1;It seems to be perfectly plausible—and so it seems equally plausible that
you'd want to do it using PL/pgSQL. Each of these two alternatives,
inspired by the advice that I got in this thread, works and produces the
expected output:
Yes, this works. This syntax is not ambiguous.
do $body$
declare
r record;
begin
select ((b, t)).*
into r
from tab1
where k = 1;
raise info 'Alt 1: % | %', r.f1::text, r.f2::text;r := (
select (b, t)
from tab1
where k = 1);
raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
end;
$body$;It feels a smidge uncomfortable because I'm forced to use reference by
field position (f1, f2) rather than by field name (b, t). But reference by
position is commonplace in PostgreSQL (for example, in the PREPARE
statement). So I'spose that I have to learn to like it.
postgres=# do $$
declare r record;
begin
select 10 as a, 20 as b into r;
raise notice '% %', r.a, r.b;
end;
$$;
NOTICE: 10 20
DO
The composite value always has structure, and types, but sometimes it can
lose labels. You can push labels by casting
r := (select (b, t) -- this is dynamic composity value, but without labels
- the scalar value doesn't hold label
or
r := (select (b, t)::type1 -- it is composite with labels again
Do you (all) think that the "Alt 1" and "Alt 2" that I show are the best
way to implement the requirement? I prefer "Alt 2" because it doesn't have
the clutter (and the burden for understanding and readability) of the extra
parentheses and the ".*".
I prefer Alt 2 too.
I can get the reference by field name that I prefer with a schema-level
type:create type tmp as (b text, t type1);
and by declaring "r" with this data type. But this is a greater discomfort
than using the dynamically shaped "record" because it needs you to create a
dedicated schema-level type for every new SELCT list that you come need.
When It is possible I use a record type - some years ago, the work with
this type was a little bit slower, but not now. The work with this type is
little bit safer - because it gets real labels. Values with declared
composite types uses positional assignment, that is not too safe.
create table foo(a int, b int);
create type footype as (a int, b int);
insert into foo values(10, 200);
postgres=# do $$
declare f footype;
r record;
begin
select b, a from foo into f;
select b, a from foo into r;
raise notice 'f.a: %, f.b: %', f.a, f.b;
raise notice 'r.a: %, r.b: %', r.a, r.b;
end;
$$;
NOTICE: f.a: 200, f.b: 10
NOTICE: r.a: 10, r.b: 200
DO
postgres=# select * from foo;
┌────┬─────┐
│ a │ b │
╞════╪═════╡
│ 10 │ 200 │
└────┴─────┘
(1 row)
But sometimes explicit type is necessary - when you want to return
composite value and when you want to work with composite outside function,
or when you want to serialize, or deserialize composite value to/from json.
When you work with composite values, is good to enable warnings
https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
Regards
Pavel
I can get the reference by field name that I prefer with a schema-level
type:create type tmp as (b text, t type1);
and by declaring "r" with this data type. But this is a greater
discomfort than using the dynamically shaped "record" because it needs you
to create a dedicated schema-level type for every new SELCT list that you
come need.When It is possible I use a record type - some years ago, the work with
this type was a little bit slower, but not now. The work with this type is
little bit safer - because it gets real labels. Values with declared
composite types uses positional assignment, that is not too safe.create table foo(a int, b int);
create type footype as (a int, b int);
insert into foo values(10, 200);postgres=# do $$
declare f footype;
r record;
begin
select b, a from foo into f;
select b, a from foo into r;
raise notice 'f.a: %, f.b: %', f.a, f.b;
raise notice 'r.a: %, r.b: %', r.a, r.b;
end;
$$;
NOTICE: f.a: 200, f.b: 10
NOTICE: r.a: 10, r.b: 200
DO
postgres=# select * from foo;
┌────┬─────┐
│ a │ b │
╞════╪═════╡
│ 10 │ 200 │
└────┴─────┘
(1 row)But sometimes explicit type is necessary - when you want to return
composite value and when you want to work with composite outside function,
or when you want to serialize, or deserialize composite value to/from json.When you work with composite values, is good to enable warnings
https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
static composite types can be good when you use dynamic SQL. The
plpgsql_check cannot derive output composite type from dynamic SQL. And it
can stop checking. When you use static composite type, then the check can
continue.
Regards
Pavel
Show quoted text
Regards
Pavel
po 9. 8. 2021 v 23:13 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
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.STRICTMULTIASSIGNMENT would detect most cases of this, except that
the condition is checked too late. We'd need to count the fields
*before* trying to assign values, not after.
I use some fragments of this routine. But the problem was so I did implicit
unnesting, although plpgsql doesn't do this
https://github.com/okbob/plpgsql_check/commit/c06c9e3dbf175c8d7d5b1df20e01dc3fea339281
postgres=# create or replace function broken_into()
returns void as $$
declare v typ2;
begin
-- should to fail
select (10,20)::typ2 into v;
-- should be ok
select ((10,20)::typ2).* into v;
-- should to fail
execute 'select (10,20)::typ2' into v;
-- should be ok
execute 'select ((10,20)::typ2).*' into v;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from plpgsql_check_function('broken_into', fatal_errors
=> false);
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ plpgsql_check_function
│
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ error:42804:5:SQL statement:cannot cast composite value of "typ2" type to
a scalar value of "integer" type │
│ warning:00000:5:SQL statement:too few attributes for composite variable
│
│ error:42804:9:EXECUTE:cannot cast composite value of "typ2" type to a
scalar value of "integer" type │
│ warning:00000:9:EXECUTE:too few attributes for composite variable
│
│ warning extra:00000:2:DECLARE:never read variable "v"
│
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
Regards
Pavel
Show quoted text
In the meantime, it does seem like the docs could be more explicit
about this, and perhaps give an example showing the (x).* solution.regards, tom lane
út 10. 8. 2021 v 21:25 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
I can get the reference by field name that I prefer with a schema-level
type:create type tmp as (b text, t type1);
and by declaring "r" with this data type. But this is a greater
discomfort than using the dynamically shaped "record" because it needs you
to create a dedicated schema-level type for every new SELCT list that you
come need.When It is possible I use a record type - some years ago, the work with
this type was a little bit slower, but not now. The work with this type is
little bit safer - because it gets real labels. Values with declared
composite types uses positional assignment, that is not too safe.create table foo(a int, b int);
create type footype as (a int, b int);
insert into foo values(10, 200);postgres=# do $$
declare f footype;
r record;
begin
select b, a from foo into f;
select b, a from foo into r;
raise notice 'f.a: %, f.b: %', f.a, f.b;
raise notice 'r.a: %, r.b: %', r.a, r.b;
end;
$$;
NOTICE: f.a: 200, f.b: 10
NOTICE: r.a: 10, r.b: 200
DO
postgres=# select * from foo;
┌────┬─────┐
│ a │ b │
╞════╪═════╡
│ 10 │ 200 │
└────┴─────┘
(1 row)But sometimes explicit type is necessary - when you want to return
composite value and when you want to work with composite outside function,
or when you want to serialize, or deserialize composite value to/from json.
There are a lot of use cases for static composite types. Everywhere on the
interface.
http://okbob.blogspot.com/2013/10/using-custom-composite-types-in.html
Show quoted text
When you work with composite values, is good to enable warnings
https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
static composite types can be good when you use dynamic SQL. The
plpgsql_check cannot derive output composite type from dynamic SQL. And it
can stop checking. When you use static composite type, then the check can
continue.Regards
Pavel
Regards
Pavel
pavel.stehule@gmail.com wrote:
bryn@yugabyte.com <mailto:bryn@yugabyte.com> wrote:
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com> wrote:
tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us> wrote:
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com> wrote:
Some errors like this, but not this can be detected by plpgsql_check
https://github.com/okbob/plpgsql_check <https://www.google.com/url?q=https://github.com/okbob/plpgsql_check&source=gmail-imap&ust=1629227921000000&usg=AOvVaw3Et9tiGoSScn4bG0DPyF8J>
probably the heuristic for type check is not complete.STRICTMULTIASSIGNMENT would detect most cases of this, except that the condition is checked too late. We'd need to count the fields *before* trying to assign values, not after.
In the meantime, it does seem like the docs could be more explicit about this, and perhaps give an example showing the (x).* solution.
Yes, a more detailed explanation of this behavior can be nice. There can be an example of value unnesting, but I think so for this case, there should be mainly an example of ANSI assign syntax.
var := (SELECT x FROM ..)
This syntax has advantages so is not amigonuous for this case, and explicit unnesting is not necessary (and it is not possible). Moreover, this is ANSI SQL syntax.
Consider this example:
create type type1 as (a1 int, a2 int);
create table tab1(k int primary key, b boolean not null, t type1 not null);
insert into tab1(k, b, t) values(1, true, (10, 20));
select b::text, t::text from tab1 where k = 1;It seems to be perfectly plausible—and so it seems equally plausible that you'd want to do it using PL/pgSQL. Each of these two alternatives, inspired by the advice that I got in this thread, works and produces the expected output:
Yes, this works. This syntax is not ambiguous.
do $body$
declare
r record;
begin
select ((b, t)).*
into r
from tab1
where k = 1;
raise info 'Alt 1: % | %', r.f1::text, r.f2::text;r := (
select (b, t)
from tab1
where k = 1);
raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
end;
$body$;It feels a smidge uncomfortable because I'm forced to use reference by field position (f1, f2) rather than by field name (b, t). But reference by position is commonplace in PostgreSQL (for example, in the PREPARE statement). So I'spose that I have to learn to like it.
postgres=# do $$
declare r record;
begin
select 10 as a, 20 as b into r;
raise notice '% %', r.a, r.b;
end;
$$;
NOTICE: 10 20
DOThe composite value always has structure, and types, but sometimes it can lose labels. You can push labels by casting
r := (select (b, t) -- this is dynamic composity value, but without labels - the scalar value doesn't hold label
or
r := (select (b, t)::type1 -- it is composite with labels again
Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is composite with labels again". I tried this:
create procedure p(i in int)
language plpgsql
as $body$
declare
r record;
begin
case i
when 1 then
select (b, t)::type1
into r
from tab1
where k = 1;
when 2 then
r := (
select (b, t)::type1
from tab1
where k = 1);
else null;
end case;
end;
$body$;
call p(3);
call p(2);
call p(1);
My idea with using a procedure and choosing which code path is followed at run-time is to distinguish between compile-time errors (there are none here) and run-time errors. Of course, "call p(3)" finishes with no error.
But both the other calls cause the same error:
42846: cannot cast type record to type1
But you say that this should work!
r := (select (b, t)::type1 -- it is composite with labels again
postgres=# do $$
declare r record;
begin
r := (select (10,20));
raise notice '%', to_json(r);
end;
$$;
NOTICE: {"f1":10,"f2":20}
DO
postgres=# do $$
declare r record;
begin
r := (select (10,20)::footype);
raise notice '%', to_json(r);
end;
$$;
NOTICE: {"a":10,"b":20}
DO
Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is
composite with labels again". I tried this:create procedure p(i in int)
language plpgsql
as $body$
declare
r record;
begin
case i
when 1 then
select (b, t)::type1
into r
from tab1
where k = 1;when 2 then
r := (
select (b, t)::type1
from tab1
where k = 1);else null;
end case;
end;
$body$;call p(3);
call p(2);
call p(1);My idea with using a procedure and choosing which code path is followed at
run-time is to distinguish between compile-time errors (there are none
here) and run-time errors. Of course, "call p(3)" finishes with no error.But both the other calls cause the same error:
42846: cannot cast type record to type1
the message is maybe not too intuitive, these casts are broken - you try to
cast (boolean, type1) => type1
The cast can ignore some fields from right or can add nulls from right, but
it cannot skip fields from left.
Show quoted text
But you say that this should work!
On 10-Aug-2021, at 13:12, Pavel Stehule <pavel.stehule@gmail.com> wrote:
r := (select (b, t)::type1 -- it is composite with labels again
postgres=# do $$
declare r record;
begin
r := (select (10,20));
raise notice '%', to_json(r);
end;
$$;
NOTICE: {"f1":10,"f2":20}
DO
postgres=# do $$
declare r record;
begin
r := (select (10,20)::footype);
raise notice '%', to_json(r);
end;
$$;
NOTICE: {"a":10,"b":20}
DOThanks, but I don't understand your "r := (select (b, t)::type1 -- it is composite with labels again". I tried this:
create procedure p(i in int)
language plpgsql
as $body$
declare
r record;
begin
case i
when 1 then
select (b, t)::type1
into r
from tab1
where k = 1;when 2 then
r := (
select (b, t)::type1
from tab1
where k = 1);else null;
end case;
end;
$body$;call p(3);
call p(2);
call p(1);My idea with using a procedure and choosing which code path is followed at run-time is to distinguish between compile-time errors (there are none here) and run-time errors. Of course, "call p(3)" finishes with no error.
But both the other calls cause the same error:
42846: cannot cast type record to type1
the message is maybe not too intuitive, these casts are broken - you try to cast (boolean, type1) => type1
The cast can ignore some fields from right or can add nulls from right, but it cannot skip fields from left.
I simply cannot mange this list's "partial quoting and bottom posting convention". Forgive me.
The code that I tried looked obviously broken. But it's what you said would work.
Anyway, it seems to me that what I wrote originally still stands. I can use a schema level type or a record. Each approach has its pros and cons.