Re: [BUGS] Update using sub-select table in schema

Simon Riggs <simon@2ndquadrant.com> writes:
> On Mon, 2006-10-02 at 11:38 -0400, Tom Lane wrote:
>> Postgres treats "FROM temp.a" the same as "FROM temp.a AS a",

> [Which AFAICS doesn't follow SQL:2003, since a reference to temp.a
> should not be allowed following its redefinition as a.]

I'm sorry, I phrased that sloppily.  In fact we don't treat them the
same, because with the "AS a" you can only refer to the entry as "a".
But the point here is that given "FROM temp.a" without AS, you can refer
to the entry as either "a" (if that is unique among the FROM-entries)
or "temp.a".

I came across a footnote in Date & Darwen (p. 140 in the 4th Edition)
implying that our interpretation is correct, but I still don't see where
the spec itself covers the point.

The comments for refnameRangeTblEntry indicate that at one time we
thought we saw support for our approach in the spec:
* An unqualified refname (schemaname == NULL) can match any RTE with matching* alias, or matching unqualified relname
inthe case of alias-less relation* RTEs.  It is possible that such a refname matches multiple RTEs in the* nearest
nestinglevel that has a match; if so, we report an error via* ereport().** A qualified refname (schemaname != NULL) can
onlymatch a relation RTE* that (a) has no alias and (b) is for the same relation identified by* schemaname.refname.
Inthis case we convert schemaname.refname to a* relation OID and search by relid, rather than by alias name.  This is*
peculiar,but it's what SQL92 says to do.
 
        regards, tom lane