X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 72FDAD1B567
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Sun,  2 Nov 2003 21:35:31 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 17777-01
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Sun,  2 Nov 2003 17:35:02 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 17A5CD1B8B0
	for <pgsql-general@postgresql.org>;
	Sun,  2 Nov 2003 17:35:00 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hA2LYG19000692;
	Sun, 2 Nov 2003 16:34:17 -0500 (EST)
To: nzanella@cs.mun.ca (Neil Zanella)
Cc: pgsql-general@postgresql.org
Subject: Re: AS operator and subselect result names: PostgreSQL vs. Oracle 
In-reply-to: <b68d2f19.0310312044.3c98da8d@posting.google.com> 
References: <b68d2f19.0310312044.3c98da8d@posting.google.com>
Comments: In-reply-to nzanella@cs.mun.ca (Neil Zanella)
	message dated "31 Oct 2003 20:44:19 -0800"
Date: Sun, 02 Nov 2003 16:34:16 -0500
Message-ID: <691.1067808856@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/29
X-Sequence-Number: 51679

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