Re: Update using sub-select table in schema

Simon Riggs <simon(at)2ndquadrant(dot)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 in the case of alias-less relation
* RTEs. It is possible that such a refname matches multiple RTEs in the
* nearest nesting level that has a match; if so, we report an error via
* ereport().
*
* A qualified refname (schemaname != NULL) can only match a relation RTE
* that (a) has no alias and (b) is for the same relation identified by
* schemaname.refname. In this 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