Re: Do FROM items of different schemas conflict? - Mailing list pgsql-hackers

Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > If you had
> >      SELECT * FROM schema1.tab1, schema2.tab2;
> > you could abbreviate:
> > SELECT tab1.col1, tab2.col5 FROM schema1.tab1, schema2.tab2 WHERE...
> 
> > i.e., as long as it is not ambiguous you can omit the schema
> > qualification.
> 
> What I am wondering about is how you tell whether it is ambiguous.
> 

Ambiguous == "found more than one match in the list of tables in FROM"


> In particular, if schema1 is not in the search path then I do not
> see how the spec can be read to say that "tab1.col1" matches "FROM
> schema1.tab1" (note no AS here).  It does seem that everyone agrees that
> that is the meaning --- there is a footnote in Date that shows he thinks
> so too 

I will really need some time to see which clauses in the spec can be 
interpreted that way.  But what people seem to believe is that the
match should occur whenever possible (i.e., unless is ambiguous).

> --- but as far as I can tell this directly contradicts the text
> of the spec, because there is noplace that says how to match an
> unqualified "tab1" against the qualified "schema1.tab1", except for
> 5.4-10 which would clearly disallow such a match.  Where am I missing
> something?
> 

Yes, read in solation it looks like it forbids it (darn, I wish I had
the standard here).  But remember that the only place you should look
for a match for "tab1" is in the things listed in the FROM clause.
The POSTQUEL extension of adding the tables for you (if I understood
right) is an aberration (if it is still supported it will ave to be
removed).

As your namespace is now restricted to the FROM clause it is easy to
see what would be the "implicit" schema you'll give to "tab1" -- the
only one that you find prefixing a tab1 in te FROM list.  If you find
more than one there is ambiguity, so it is not allowed -- AS clause
is required then.



> >> These rules essentially say that a FROM entry "FROM foo.bar" is exactly
> >> equivalent to "FROM foo.bar AS bar",
> 
> > A small difference.  With the first you can refer to columns as
> 
> > foo.bar.col1
> 
> > with the second you cannot.  You must say:  bar.col1
> 
> Well, the point is that I would like to allow that, specifically because
> I would like to say that the equivalence is exact.  I don't see any
> value in enforcing this particular nitpick.
> 

But you must.  As soon as you used "AS bar" it is not a table
name anymore, i.e., it cannot be qualified by an schema.  It is
a correlation name which is a single unqualified name and whatever
refers to it _must_ use only that single name.  So "foo.bar.col1"
makes absolute no sense after you say "AS bar".



-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9