On Fri, Mar 10, 2023 at 1:29 PM Bryn Llewellyn <b...@yugabyte.com> wrote:
> > david.g.johns...@gmail.com wrote: > > > >> b...@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: 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.
