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

On Thu, Mar 9, 2023 at 12:46 PM Bryn Llewellyn <bryn(at)yugabyte(dot)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...

David J.