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 09A0BD1B907
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Sun,  2 Nov 2003 22:17:18 +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 25878-06
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Sun,  2 Nov 2003 18:16:49 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 4A4ADD1B8B5
	for <pgsql-general@postgresql.org>;
	Sun,  2 Nov 2003 18:16:47 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 40B8535466; Sun,  2 Nov 2003 14:16:34 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 3F3E73544B; Sun,  2 Nov 2003 14:16:34 -0800 (PST)
Date: Sun, 2 Nov 2003 14:16:34 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Neil Zanella <nzanella@cs.mun.ca>
Cc: pgsql-general@postgresql.org
Subject: Re: AS operator and subselect result names: PostgreSQL
In-Reply-To: <b68d2f19.0310312044.3c98da8d@posting.google.com>
Message-ID: <20031102141232.P85650@megazone.bigpanda.com>
References: <b68d2f19.0310312044.3c98da8d@posting.google.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/27
X-Sequence-Number: 51677

On Fri, 31 Oct 2003, Neil Zanella wrote:

> Hello,
>
> I would like to ask the about the following...
>
> 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. In particular:
>
> 1. Does standard SQL allow an optional AS keyword for (re/)naming
>    tables including those resulting from subselects.
>
> and
>
> 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.

I believe the section in question of SQL92 that you're asking about
says explicitly that a table reference from a derived table should look
like:
<derived table> [ AS ] <correlation name> [ <left paren> <derived column
list> <right paren> ]
where <derived table> is a table subquery.

It's possible that SQL99 changes this, but in SQL92 at least, it looks
like the correlation name is not optional (although the AS keyword is).