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

On Fri, Mar 10, 2023 at 1:29 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> > david(dot)g(dot)johnston(at)gmail(dot)com wrote:
> >
> >> 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...
>
> 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:

https://www.postgresql.org/docs/current/catalog-pg-type.html

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.