david.g.johnston@xxxxxxxxx wrote:bryn@xxxxxxxxxxxx wrote: Thanks for the links, David. I had earlier written this: "select (17, 42)::s.t2 into r2" doesn't work and Tom replied thus: [use] "select 17, 42 into r2". Thanks, I see this now. This text from reference [2] explains it: « 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. Tom's "don't use SELECT INTO; use an assignment statement" is fine when the restriction guarantees to produce exactly one row. But otherwise, you need a "cursor for loop". Further, "an assignment statement" works only when the RHS is a scalar subquery. But sometimes you want a "select list" with more than one column—where any/all can have multi-valued data types. It seems to me that "select into" or a "cursor for loop", where each has a single target declared as "record" is the most general approach. I tried yet another test. Here's the set-up: create type s.x as (a1 int, a2 text); -- Write my own constrctor. create function s.x(a1_in in int, a2_in in text) returns s.x language plpgsql as $body$ declare r s.x; begin r.a1 := a1_in; r.a2 := a2_in; return r; end; $body$; do $body$ declare v1 constant s.x not null := s.x(17, 'dog'::text); v2 constant s.x not null := (17, 'dog'::text)::s.x; begin assert v1 = v2; end; $body$; create table s.t(k int primary key, c1 s.x, c2 int[]); insert into s.t(k, c1, c2) values (1, (17, 'cat'::text)::s.x, array[11, 12, 13]), (2, (42, 'dog'::text)::s.x, array[21, 22, 23]); The aim, here, was to demonstrate once and for all that (in this example), "(17, 'dog'::text)::s.x" is a perfectly fine type constructor for "s.x". There seems to be another documentation gap here. "Array constructor" is a well-defined term of art; and the "array[…]" syntax implements it. But Google search, and the PG doc's own search, get nothing useful for this: 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; 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.) Anyway, back to my test… here's the rest of it. create function s.f() returns table(z text) language plpgsql as $body$ declare r s.x; arr int[]; the_row record; begin -- 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; z := r::text||' / '||arr::text; return next; z := ''; return next; select a.c1, a.c2 into the_row from s.t as a where a.k = 1; z := the_row.c1::text||' / '||the_row.c2::text; return next; z := ''; return next; 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; end; $body$; select s.f(); This is the result: (17,cat) / {11,12,13} (17,cat) / {11,12,13} (17,cat) / {11,12,13} (42,dog) / {21,22,23} |
