Re: select (17, 42)::s.t2 into... fails with "invalid input syntax" - Mailing list pgsql-general

«

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.

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.)

  -- 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;

  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;