pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bryn Llewellyn <bryn@yugabyte.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"
Date: Fri, 10 Mar 2023 12:29:39 -0800
Message-ID: <9E792FAF-873E-40AA-A3CF-A4AB55EF8230@yugabyte.com> (raw)
In-Reply-To: <CAKFQuwZ9vR4C7nT4XCUKC+FrTECER2P-M4TJ3AuFwooQpD6RyQ@mail.gmail.com>
References: <C68A39DC-A728-41A2-ADD7-A004DB4156FD@yugabyte.com>
	<281321.1678328800@sss.pgh.pa.us>
	<E9868EBC-B655-4DFE-9DC7-940C3D55339D@yugabyte.com>
	<CAKFQuwZiR4paFEjiJP4py4HpHBeur=QZqi7Ys1p_XY8-WiCEYw@mail.gmail.com>
	<483AF2DD-BBC9-4C73-9418-8231948F4C27@yugabyte.com>
	<CAKFQuwZ9vR4C7nT4XCUKC+FrTECER2P-M4TJ3AuFwooQpD6RyQ@mail.gmail.com>

> 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 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? 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 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.) So it seems wrong to call this a "row type" because doing so leaves no term of art left over for the ephemeral 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 (…)"?






view thread (9+ messages)  latest in thread

Message-ID: <9E792FAF-873E-40AA-A3CF-A4AB55EF8230@yugabyte.com>
Permalink:  ../9E792FAF-873E-40AA-A3CF-A4AB55EF8230@yugabyte.com/
Also on:    postgresql.org/message-id/9E792FAF-873E-40AA-A3CF-A4AB55EF8230@yugabyte.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: bryn@yugabyte.com, david.g.johnston@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"
  In-Reply-To: <9E792FAF-873E-40AA-A3CF-A4AB55EF8230@yugabyte.com>
  References: <C68A39DC-A728-41A2-ADD7-A004DB4156FD@yugabyte.com> <281321.1678328800@sss.pgh.pa.us> <E9868EBC-B655-4DFE-9DC7-940C3D55339D@yugabyte.com> <CAKFQuwZiR4paFEjiJP4py4HpHBeur=QZqi7Ys1p_XY8-WiCEYw@mail.gmail.com> <483AF2DD-BBC9-4C73-9418-8231948F4C27@yugabyte.com> <CAKFQuwZ9vR4C7nT4XCUKC+FrTECER2P-M4TJ3AuFwooQpD6RyQ@mail.gmail.com> <9E792FAF-873E-40AA-A3CF-A4AB55EF8230@yugabyte.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox