From: Tom Lane <tgl@sss.pgh.pa.us>
To: nzanella@cs.mun.ca
Cc: pgsql-general@postgresql.org
Subject: Re: AS operator and subselect result names: PostgreSQL vs. Oracle
Date: Sun, 02 Nov 2003 16:34:16 -0500
Message-ID: <691.1067808856@sss.pgh.pa.us> (raw)
In-Reply-To: <b68d2f19.0310312044.3c98da8d@posting.google.com>
References: <b68d2f19.0310312044.3c98da8d@posting.google.com>
nzanella@cs.mun.ca (Neil Zanella) writes:
> PostgreSQL allows tables resulting from subselects to be renamed with
> an optional AS keyword whereas Oracle 9 will report an error whenever
> a table is renamed with the AS keyword. Furthermore, in PostgreSQL
> when the result of a subselect is referenced in an outer select
> it is required that the subselect result be named, whereas this
> is not true in Oracle. I wonder what standard SQL has to say
> about these two issues.
The standard agrees with us.
SQL99 section 7.5 <from clause> says that FROM clause items are
<table reference>s:
<from clause> ::=
FROM <table reference list>
<table reference list> ::=
<table reference> [ { <comma> <table reference> }... ]
the syntax for which appears in 7.6 <table reference>:
<table reference> ::=
<table primary>
| <joined table>
<table primary> ::=
<table or query name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <lateral derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <collection derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <only spec>
[ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <left paren> <joined table> <right paren>
<derived table> ::= <table subquery>
[ I've omitted the definitions for other cases ]
and in 7.14 we find
<table subquery> ::= <subquery>
<subquery> ::=
<left paren> <query expression> <right paren>
So the second alternative (<derived table> ...) is the one that allows a
sub-select.
Notice that the AS-clause ([ AS ] <correlation name> [ <left paren>
<derived column list> <right paren> ]) is bracketed as a whole, making
it optional, in just two of the five alternatives where it appears.
It is required by the syntax in the <derived table> case.
> 1. Does standard SQL allow an optional AS keyword for (re/)naming
> tables including those resulting from subselects.
It does not "allow" it, it requires it.
> 2 Why must a subselect whose fields are referenced in an outer query
> be explicitly named in PostgreSQL when it is not necessary in Oracle.
We insist on a name because otherwise we'd have to invent a name for the
FROM-clause item, and in most cases there's not an obvious choice for a
default name. I dunno what Oracle does about choosing a name, but it's
not standard behavior.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, nzanella@cs.mun.ca
Subject: Re: AS operator and subselect result names: PostgreSQL vs. Oracle
In-Reply-To: <691.1067808856@sss.pgh.pa.us>
* 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