On Fri, Apr 26, 2024 at 2:25 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: > > No other programming language that I know of, and no other database > > that I know of, looks at x.y.z and says "ok, well first we have to > > figure out whether the object is named x or x.y or x.y.z, and then > > after that, we'll use whatever is left over as a field selector." > > It may indeed be true that nobody but SQL does that, but nonetheless > this is exactly what SQL99 requires AFAICT. The reason we use this > parenthesis notation is precisely that we didn't want to get into > that sort of tea-leaf-reading about how many identifiers mean what. > The parens put it on the user to tell us what part of the chain > is field selection.
I really thought this was just PostgreSQL, not SQL generally, but I just experimented a bit with Oracle on dbfiddle.uk using this example: CREATE TYPE foo AS OBJECT (a number(10), b varchar2(2000)); CREATE TABLE bar (quux foo); INSERT INTO bar VALUES (foo(1, 'one')); SELECT bar.quux, quux, (quux).a, (bar.quux).a FROM bar; This works, but if I delete the parentheses from the last line, then it fails. So evidently my understanding of how this works in other systems is incorrect, or incomplete. I feel like I've encountered cases where we required extra parenthesization that Oracle didn't need, but it's hard to discuss that without examples, and I don't have them right now. > Yes, we can. Please do not rant further about this until you've > read the <identifier chain> section of a recent SQL spec. I'm hurt to see emails that I spent time on characterized as a rant, even if I was wrong on the facts. And I think appealing to the SQL standard is a poor way of trying to end debate on a topic. -- Robert Haas EDB: http://www.enterprisedb.com
