On Thu, Mar 9, 2023 at 12:46 PM Bryn Llewellyn <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... David J.
