On Fri, Mar 10, 2023 at 1:29 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
> david.g.johnston@xxxxxxxxx wrote:
>
>> bryn@xxxxxxxxxxxx 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.
