Обсуждение: select (17, 42)::s.t2 into... fails with "invalid input syntax"
I have a horrible feeling that I'm missing the blindingly obvious here. But I can't spot it. Help!
This simple setup produces the expected result:
create type s.t1 as (c1 text, c2 text);
select ('cat', 'dog')::s.t1;
select ('cat', 'dog')::s.t1;
This is the result:
(cat,dog)
create type s.t2 as (c1 int, c2 int);
select (17, 42)::s.t2;
select (17, 42)::s.t2;
This is the result:
(17,42)
(I know that plsql is doing an under-the-covers typecast to "text" to display the result. The error (or at least, to me, shock) comes when I bring PL/pgSQL into the picture:
create function s.f()
returns table(z text)
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
r1 s.t1;
r2 s.t2;
txt text;
begin
r1 := (select ('cat', 'dog')::s.t1);
z := '1: '||r1.c1||' / '||r1.c2; return next;
select ('cat', 'dog')::s.t1 into r1;
z := '2: '||r1.c1||' / '||coalesce(r1.c2, '<NULL>'); return next;
r2 := (select (17, 42)::s.t2);
z := '3: '||(r2.c1)::text||' / '||(r2.c2)::text; return next;
begin
select (17, 42)::s.t2 into r2;
exception
-- invalid input syntax for type integer: "(17,42)"
when invalid_text_representation then
z := 'invalid_text_representation caught'; return next;
end;
select (17, 42)::s.t2 into txt;
r2 := txt;
z := '4: '||(r2.c1)::text||' / '||(r2.c2)::text; return next;
end;
$body$;
select s.f();
txttxt
returns table(z text)
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
r1 s.t1;
r2 s.t2;
txt text;
begin
r1 := (select ('cat', 'dog')::s.t1);
z := '1: '||r1.c1||' / '||r1.c2; return next;
select ('cat', 'dog')::s.t1 into r1;
z := '2: '||r1.c1||' / '||coalesce(r1.c2, '<NULL>'); return next;
r2 := (select (17, 42)::s.t2);
z := '3: '||(r2.c1)::text||' / '||(r2.c2)::text; return next;
begin
select (17, 42)::s.t2 into r2;
exception
-- invalid input syntax for type integer: "(17,42)"
when invalid_text_representation then
z := 'invalid_text_representation caught'; return next;
end;
select (17, 42)::s.t2 into txt;
r2 := txt;
z := '4: '||(r2.c1)::text||' / '||(r2.c2)::text; return next;
end;
$body$;
select s.f();
txttxt
It produces this output:
1: cat / dog
2: (cat,dog) / <NULL>
3: 17 / 42
invalid_text_representation caught
4: 17 / 42
2: (cat,dog) / <NULL>
3: 17 / 42
invalid_text_representation caught
4: 17 / 42
Results #1 and #3, from "UDT-value := (scaler subquery)", are what I expected.
Result #2 tells me what seems to be going on—and it dumbfounds me. The first text field of my UDT value got "(cat,dog)"; and now that all input values have been consumed, "c2" got NULL.
Do you (all) expect this? And if so, what's the story?
This outcome seems to explain the error. The text value "(17,42)" for "c1", spirited up from "(17, 42)::s.t2", can't be converted to an integer.
Yet more mysterious is why the workaround, go via an intermediate text value, succeeds:
select (17, 42)::s.t2 into txt;
r2 := txt;
r2 := txt;
But if I compress it thus:
select (((17, 42)::s.t2)::text)::s.t2 into r2;
then I'm back to the same 22P02 error:
invalid input syntax for type integer: "(17,42)"
On Wed, Mar 8, 2023 at 5:41 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
select (((17, 42)::s.t2)::text)::s.t2 into r2;then I'm back to the same 22P02 error:invalid input syntax for type integer: "(17,42)"
Single quotes. SELECT '(17,42)'::s.t2;
David J.
Bryn Llewellyn <bryn@yugabyte.com> writes:
> select (17, 42)::s.t2 into r2;
> [ doesn't work ]
This would work as
select 17, 42 into r2;
In general, SELECT INTO with a composite target expects to see
a source column per target field. If you want to assign a
composite value to the whole target, don't use SELECT INTO;
use an assignment statement.
regards, tom lane
tgl@sss.pgh.pa.us wrote:bryn@yugabyte.com writes:
select (17, 42)::s.t2 into r2;
[ doesn't work ]
This would work as
select 17, 42 into r2;
In general, SELECT INTO with a composite target expects to see a source column per target field. If you want to assign a
composite value to the whole target, don't use SELECT INTO; use an assignment statement.
Thanks for the clue. Your "select 17, 42 into r2" does indeed work. But I can't find a way to act on your "If you want to assign a
composite value to the whole target… use an assignment statement".
Here's my test. It's closer to what I wanted to do. I wanted to assign a value to a PL/pgSQL variable whose data type is a composite type from component values from a table. And I happened to start off with "select into" rather than with a subquery on the RHS of an assignment. In another context, I want to construct a value of my composite type from variables that represent its components.
create table s.t(k int primary key, c1 int, c2 int);
insert into s.t(k, c1, c2) values(1, 17, 42);
create type s.x as (c1 int, c2 int);
create function s.f()
returns table(z text)
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
my_c1 int := 17;
my_c2 int := 42;
r s.x;
txt text;
begin
r := (select (a.c1, a.c2)::s.x from s.t a where a.k = 1);
z := (r.c1)::text||' / '||(r.c2)::text; return next;
select a.c1, a.c2 into r from s.t a where a.k = 1;
z := (r.c1)::text||' / '||(r.c2)::text; return next;
begin
r := (my_c1, my_c2)::s.t;
exception when cannot_coerce then
z := '"cannot_coerce" handled.'; return next;
end;
r.c1 := my_c1;
r.c2 := my_c2;
z := (r.c1)::text||' / '||(r.c2)::text; return next;
end;
$body$;
select s.f();
This is specific to "language plpgsql" subprograms. So I should find the rules that I need to understand in "Chapter 43. PL/pgSQL - SQL Procedural Language" (www.postgresql.org/docs/11/plpgsql.html). But I can't. However, I'm not very good at finding the relevant doc when I need it. Where is it?
Going from what you said, and my most recent test, here, the rules seem to be inscrutable—and non-composable.
I wonder if it all boils down to the strange-to-me anonymous, polymorphic "record" notion.
On Wed, Mar 8, 2023 at 7:58 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
r := (my_c1, my_c2)::s.t;
If you write s.x there it will also work.
Your first and third assignments are identical in syntax/nature. These are both the first examples here[1]
Yes, the behavior of INTO in the second assignment is somewhat non-intuitive; but covered here[2]. Probably it could use more examples.
The final form fits into a procedural flow better than the SQL-based one. Since plpgsql allows for procedural flow this makes sense. The composite variable reference is simply: main_type_name.field_name Hence the second example here[1]
David J.
david.g.johnston@gmail.com wrote:bryn@yugabyte.com wrote:
r := (my_c1, my_c2)::s.t;
If you write s.x there it will also work.
Your first and third assignments are identical in syntax/nature. These are both the first examples here[1]
Yes, the behavior of INTO in the second assignment is somewhat non-intuitive; but covered here[2]. Probably it could use more examples.
The final form fits into a procedural flow better than the SQL-based one. Since plpgsql allows for procedural flow this makes sense. The composite variable reference is simply: main_type_name.field_name Hence the second example here[1]
[1] https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
[2] https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
Thanks for the links, David. I had earlier written this:
"select (17, 42)::s.t2 into r2" doesn't work
and Tom replied thus:
[use] "select 17, 42 into r2".
In general, SELECT INTO with a composite target expects to see a source column per target field. If you want to assign a
composite value to the whole target, don't use SELECT INTO; use an assignment statement.
Thanks, I see this now. This text from reference [2] explains it:
«
SELECT select_expressions INTO [STRICT] target FROM …;
where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.
»
In plain English, the "into" target cannot be a local variable whose data type is a composite type. That comes as a complete shock. Moreover, it isn't true—as Tom's advice shows. (I tried it and it worked.) I don't know what "simple" (as in "simple variable" means. I'm guessing that it means "single-valued" in the Codd-and-Date sense so that, for example, 42 and 'dog' are simple but array[1, 2, 3] isn't. My test (below), with a column "c1" and a local variable "arr", both of data type "int[]", showed that "select… c2… into arr…" works fine here. So the wording in the doc that I copied above could be improved.
Tom's "don't use SELECT INTO; use an assignment statement" is fine when the restriction guarantees to produce exactly one row. But otherwise, you need a "cursor for loop". Further, "an assignment statement" works only when the RHS is a scalar subquery. But sometimes you want a "select list" with more than one column—where any/all can have multi-valued data types.
It seems to me that "select into" or a "cursor for loop", where each has a single target declared as "record" is the most general approach.
I tried yet another test. Here's the set-up:
create type s.x as (a1 int, a2 text);
-- Write my own constrctor.
create function s.x(a1_in in int, a2_in in text)
returns s.x
language plpgsql
as $body$
declare
r s.x;
begin
r.a1 := a1_in;
r.a2 := a2_in;
return r;
end;
$body$;
do $body$
declare
v1 constant s.x not null := s.x(17, 'dog'::text);
v2 constant s.x not null := (17, 'dog'::text)::s.x;
begin
assert v1 = v2;
end;
$body$;
create table s.t(k int primary key, c1 s.x, c2 int[]);
insert into s.t(k, c1, c2) values
(1, (17, 'cat'::text)::s.x, array[11, 12, 13]),
(2, (42, 'dog'::text)::s.x, array[21, 22, 23]);
-- Write my own constrctor.
create function s.x(a1_in in int, a2_in in text)
returns s.x
language plpgsql
as $body$
declare
r s.x;
begin
r.a1 := a1_in;
r.a2 := a2_in;
return r;
end;
$body$;
do $body$
declare
v1 constant s.x not null := s.x(17, 'dog'::text);
v2 constant s.x not null := (17, 'dog'::text)::s.x;
begin
assert v1 = v2;
end;
$body$;
create table s.t(k int primary key, c1 s.x, c2 int[]);
insert into s.t(k, c1, c2) values
(1, (17, 'cat'::text)::s.x, array[11, 12, 13]),
(2, (42, 'dog'::text)::s.x, array[21, 22, 23]);
The aim, here, was to demonstrate once and for all that (in this example), "(17, 'dog'::text)::s.x" is a perfectly fine type constructor for "s.x". There seems to be another documentation gap here. "Array constructor" is a well-defined term of art; and the "array[…]" syntax implements it. But Google search, and the PG doc's own search, get nothing useful for this:
postgresql composite type constructor
For example, "8.16. Composite Types" (www.postgresql.org/docs/current/rowtypes.html#id-1.5.7.24.6) talks only about anonymous "row". And this little test seems to show that "row" and "record" mean the same thing—but you seed to write (or you see) different spellings in different contexts:
with
c(r) as (values(row(1, 'a', true)))
select c.r, pg_typeof(c.r) from c;
c(r) as (values(row(1, 'a', true)))
select c.r, pg_typeof(c.r) from c;
Confusing, eh? There seems to be some doc missing here too that defines "type constructor" and that uses "(f1, ..fn)::qualified_type_id". (The "create type" account should x-ref it.)
Anyway, back to my test… here's the rest of it.
create function s.f()
returns table(z text)
language plpgsql
as $body$
declare
r s.x;
arr int[];
the_row record;
begin
-- Tom's approach. Not nice.
-- Two separate "select" statements to avoid
-- 42601: record variable cannot be part of multiple-item INTO list.
select (a.c1).a1, (a.c1).a2 into r from s.t as a where a.k = 1;
select a.c2 into arr from s.t as a where a.k = 1;
z := r::text||' / '||arr::text; return next;
z := ''; return next;
select a.c1, a.c2 into the_row from s.t as a where a.k = 1;
z := the_row.c1::text||' / '||the_row.c2::text; return next;
z := ''; return next;
for the_row in (select a.c1, a.c2 from s.t as a order by a.k) loop
z := the_row.c1::text||' / '||the_row.c2::text; return next;
end loop;
end;
$body$;
select s.f();
returns table(z text)
language plpgsql
as $body$
declare
r s.x;
arr int[];
the_row record;
begin
-- Tom's approach. Not nice.
-- Two separate "select" statements to avoid
-- 42601: record variable cannot be part of multiple-item INTO list.
select (a.c1).a1, (a.c1).a2 into r from s.t as a where a.k = 1;
select a.c2 into arr from s.t as a where a.k = 1;
z := r::text||' / '||arr::text; return next;
z := ''; return next;
select a.c1, a.c2 into the_row from s.t as a where a.k = 1;
z := the_row.c1::text||' / '||the_row.c2::text; return next;
z := ''; return next;
for the_row in (select a.c1, a.c2 from s.t as a order by a.k) loop
z := the_row.c1::text||' / '||the_row.c2::text; return next;
end loop;
end;
$body$;
select s.f();
This is the result:
(17,cat) / {11,12,13}
(17,cat) / {11,12,13}
(17,cat) / {11,12,13}
(42,dog) / {21,22,23}
(17,cat) / {11,12,13}
(17,cat) / {11,12,13}
(42,dog) / {21,22,23}
On Thu, Mar 9, 2023 at 12:46 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
«SELECT select_expressions INTO [STRICT] target FROM …;where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.»In plain English, the "into" target cannot be a local variable whose data type is a composite type. That comes as a complete shock. Moreover, it isn't true—as Tom's advice shows. (I tried it and it worked.) I don't know what "simple" (as in "simple variable" means. I'm guessing that it means "single-valued" in the Codd-and-Date sense so that, for example, 42 and 'dog' are simple but array[1, 2, 3] isn't. My test (below), with a column "c1" and a local variable "arr", both of data type "int[]", showed that "select… c2… into arr…" works fine here. So the wording in the doc that I copied above could be improved.
Reading the meaning of "simple" to be "not record or row variables" seems like the safe bet, since those are covered in the first part of the sentence. As a composite type is the umbrella term covering both record and row that sentence most certainly does allow for such a variable to be the target. But when it is, each individual column of the result gets mapped to individual fields of the composite type. This seems like a reasonable use case to define behavior from.
postgresql composite type constructorFor example, "8.16. Composite Types" (www.postgresql.org/docs/current/rowtypes.html#id-1.5.7.24.6) talks only about anonymous "row". And this little test seems to show that "row" and "record" mean the same thing—but you seed to write (or you see) different spellings in different contexts:with
c(r) as (values(row(1, 'a', true)))
select c.r, pg_typeof(c.r) from c;
Composite types that don't have a system-defined name are instead named "record". "Row" usually means that not only is the composite type named but the name matches that of a table in the system. IOW, as noted above, "composite type" is a type category or umbrella that covers all of these cases.
Confusing, eh? There seems to be some doc missing here too that defines "type constructor" and that uses "(f1, ..fn)::qualified_type_id". (The "create type" account should x-ref it.)
You just pointed to the relevant documentation, and adding it to create type doesn't seem like a good fit but if someone wanted to I'm not sure I'd veto it.
-- Tom's approach. Not nice.
-- Two separate "select" statements to avoid
-- 42601: record variable cannot be part of multiple-item INTO list.
select (a.c1).a1, (a.c1).a2 into r from s.t as a where a.k = 1;
select a.c2 into arr from s.t as a where a.k = 1;
Yeah, I can see this as a natural consequence of the "column per field" behavior decision. Maybe it should be covered better in the docs? Seems like an unfortunate corner-case annoyance seldom encountered due to the general disuse of composite types.
for the_row in (select a.c1, a.c2 from s.t as a order by a.k) loop
z := the_row.c1::text||' / '||the_row.c2::text; return next;
end loop;
Doesn't seem like a terrible work-around even in the single-row case...
David J.
> david.g.johnston@gmail.com wrote: > >> bryn@yugabyte.com wrote: >> >> « >> SELECT select_expressions INTO [STRICT] target FROM …; >> >> where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. >> » >> >> In plain English, the "into" target cannot be a local variable whose data type is a composite type. That comes as a completeshock. Moreover, it isn't true—as Tom's advice shows. (I tried it and it worked.) I don't know what "simple" (asin "simple variable" means. I'm guessing that it means "single-valued" in the Codd-and-Date sense so that, for example,42 and 'dog' are simple but array[1, 2, 3] isn't. My test (below), with a column "c1" and a local variable "arr",both of data type "int[]", showed that "select… c2… into arr…" works fine here. So the wording in the doc that I copiedabove could be improved. > > Reading the meaning of "simple" to be "not record or row variables" seems like the safe bet, since those are covered inthe first part of the sentence. As a composite type is the umbrella term covering both record and row that sentence mostcertainly does allow for such a variable to be the target. But when it is, each individual column of the result getsmapped to individual fields of the composite type. This seems like a reasonable use case to define behavior from. > >> postgresql composite type constructor >> >> For example, "8.16. Composite Types" (www.postgresql.org/docs/current/rowtypes.html#id-1.5.7.24.6) talks only about anonymous"row". And this little test seems to show that "row" and "record" mean the same thing—but you seed to write (oryou see) different spellings in different contexts: >> >> with >> c(r) as (values(row(1, 'a', true))) >> select c.r, pg_typeof(c.r) from c; > > Composite types that don't have a system-defined name are instead named "record". "Row" usually means that not only isthe composite type named but the name matches that of a table in the system. IOW, as noted above, "composite type" isa type category or umbrella that covers all of these cases. > >> Confusing, eh? There seems to be some doc missing here too that defines "type constructor" and that uses "(f1, ..fn)::qualified_type_id".(The "create type" account should x-ref it.) > > You just pointed to the relevant documentation, and adding it to create type doesn't seem like a good fit but if someonewanted to I'm not sure I'd veto it. > >> -- Tom's approach. Not nice. >> -- Two separate "select" statements to avoid >> -- 42601: record variable cannot be part of multiple-item INTO list. >> select (a.c1).a1, (a.c1).a2 into r from s.t as a where a.k = 1; >> select a.c2 into arr from s.t as a where a.k = 1; > > Yeah, I can see this as a natural consequence of the "column per field" behavior decision. Maybe it should be coveredbetter in the docs? Seems like an unfortunate corner-case annoyance seldom encountered due to the general disuseof composite types. > >> for the_row in (select a.c1, a.c2 from s.t as a order by a.k) loop >> z := the_row.c1::text||' / '||the_row.c2::text; return next; >> end loop; > > Doesn't seem like a terrible work-around even in the single-row case... If "composite type" is the umbrella term that covers "row", "record", and the result of "create type… as (…)", what is theterm of art for the latter? The account of "pg_type.typtype" says this: « b for a base type, c for a composite type (e.g., a table's row type), d for a domain, e for an enum type, p for a pseudo-type,or r for a range type. » This wording doesn't help me because there are no DDLs to create occurrences of "row" or "record". And the companion fora table is a real schema-object, distinct from the table itself like the result of "create type… as (…)" using the namesand data types of the table's columns. (I'm assuming here that no particular schema-object can have a row both in pg_classand pg_type.) So it seems wrong to call this a "row type" because doing so leaves no term of art left over for theephemeral result of "row(1, 'a', true)" which pg_typeof anyway calls "record". Finally, what terms of art do PG experts use to distinguish between single-valued data types like "integer", "text", "boolean"and so on and multi--valued data types like "array", "row", "record", and the result of "create type… as (…)"?
On Fri, Mar 10, 2023 at 1:29 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> david.g.johnston@gmail.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> «
>> SELECT select_expressions INTO [STRICT] target FROM …;
>>
>> where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.
>> »
>>
>> In plain English, the "into" target cannot be a local variable whose data type is a composite type. That comes as a complete shock. Moreover, it isn't true—as Tom's advice shows. (I tried it and it worked.) I don't know what "simple" (as in "simple variable" means. I'm guessing that it means "single-valued" in the Codd-and-Date sense so that, for example, 42 and 'dog' are simple but array[1, 2, 3] isn't. My test (below), with a column "c1" and a local variable "arr", both of data type "int[]", showed that "select… c2… into arr…" works fine here. So the wording in the doc that I copied above could be improved.
>
> Reading the meaning of "simple" to be "not record or row variables" seems like the safe bet, since those are covered in the first part of the sentence. As a composite type is the umbrella term covering both record and row that sentence most certainly does allow for such a variable to be the target. But when it is, each individual column of the result gets mapped to individual fields of the composite type. This seems like a reasonable use case to define behavior from.
>
>> postgresql composite type constructor
>>
>> For example, "8.16. Composite Types" (www.postgresql.org/docs/current/rowtypes.html#id-1.5.7.24.6) talks only about anonymous "row". And this little test seems to show that "row" and "record" mean the same thing—but you seed to write (or you see) different spellings in different contexts:
>>
>> with
>> c(r) as (values(row(1, 'a', true)))
>> select c.r, pg_typeof(c.r) from c;
>
> Composite types that don't have a system-defined name are instead named "record". "Row" usually means that not only is the composite type named but the name matches that of a table in the system. IOW, as noted above, "composite type" is a type category or umbrella that covers all of these cases.
>
>> Confusing, eh? There seems to be some doc missing here too that defines "type constructor" and that uses "(f1, ..fn)::qualified_type_id". (The "create type" account should x-ref it.)
>
> You just pointed to the relevant documentation, and adding it to create type doesn't seem like a good fit but if someone wanted to I'm not sure I'd veto it.
>
>> -- Tom's approach. Not nice.
>> -- Two separate "select" statements to avoid
>> -- 42601: record variable cannot be part of multiple-item INTO list.
>> select (a.c1).a1, (a.c1).a2 into r from s.t as a where a.k = 1;
>> select a.c2 into arr from s.t as a where a.k = 1;
>
> Yeah, I can see this as a natural consequence of the "column per field" behavior decision. Maybe it should be covered better in the docs? Seems like an unfortunate corner-case annoyance seldom encountered due to the general disuse of composite types.
>
>> for the_row in (select a.c1, a.c2 from s.t as a order by a.k) loop
>> z := the_row.c1::text||' / '||the_row.c2::text; return next;
>> end loop;
>
> Doesn't seem like a terrible work-around even in the single-row case...
If "composite type" is the umbrella term that covers "row", "record", and the result of "create type… as (…)", what is the term of art for the latter?
Composite type. Language is hard.
The account of "pg_type.typtype" says this:
« b for a base type, c for a composite type (e.g., a table's row type), d for a domain, e for an enum type, p for a pseudo-type, or r for a range type. »
This wording doesn't help me because there are no DDLs to create occurrences of "row"
CREATE TABLE ... which also implicitly creates a type of the same name.
or "record".
True.
And the companion for a table is a real schema-object, distinct from the table itself like the result of "create type… as (…)" using the names and data types of the table's columns. (I'm assuming here that no particular schema-object can have a row both in pg_class and pg_type.)
You assume incorrectly.
Finally, what terms of art do PG experts use to distinguish between single-valued data types like "integer", "text", "boolean" and so on and multi--valued data types like "array", "row", "record", and the result of "create type… as (…)"?
Scalar; or if you go by the documentation, base type.
> They respectively create a composite type, an enum type, a range type, a base type, or a shell type.
Also
> A composite type is essentially the same as the row type of a table,
You can also read the description for pg_type:
In particular:
> Base types and enum types (scalar types) are created with CREATE TYPE
and
> A composite type is automatically created for each table in the database
David J.