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

> david.g.johnston@gmail.com wrote:
>
>> bryn@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
completeshock. Moreover, it isn't true—as Tom's advice shows. (I tried it and it worked.) I don't know what "simple"
(asin "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
copiedabove could be improved. 
>
> Reading the meaning of "simple" to be "not record or row variables" seems like the safe bet, since those are covered
inthe first part of the sentence.  As a composite type is the umbrella term covering both record and row that sentence
mostcertainly does allow for such a variable to be the target.  But when it is, each individual column of the result
getsmapped 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
(oryou 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
isthe composite type named but the name matches that of a table in the system.  IOW, as noted above, "composite type"
isa 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
someonewanted 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
coveredbetter in the docs?  Seems like an unfortunate corner-case annoyance seldom encountered due to the general
disuseof 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
theterm of art for the latter? 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" or "record". And the companion
fora table is a real schema-object, distinct from the table itself like the result of "create type… as (…)" using the
namesand data types of the table's columns. (I'm assuming here that no particular schema-object can have a row both in
pg_classand pg_type.) So it seems wrong to call this a "row type" because doing so leaves no term of art left over for
theephemeral result of "row(1, 'a', true)" which pg_typeof anyway calls "record". 

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 (…)"?