Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)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]);

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;

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();

This is the result:

(17,cat) / {11,12,13}

(17,cat) / {11,12,13}

(17,cat) / {11,12,13}
(42,dog) / {21,22,23}