Re: Oracle Style packages on postgres

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, May 10, 2005 11:42 PM
> To: Bruce Momjian
> Cc: Dave Held; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Oracle Style packages on postgres
>
> [...]
> There's been a lot of handwaving about nested schemas in this thread,
> but no one has explained how they could actually *work* given the SQL
> syntax rules. In general, "a" is a column from the current table
> set, "a.b" is a column b in table/alias a from the current query,
> "a.b.c" is a column c from table b in schema a, "a.b.c.d" is a column
> d from table c in schema b in catalog a, and any more than that is
> a syntax error. I do not see how to add nested schemas
> without creating unworkable ambiguities, not to say outright violations
> of the spec.

Clearly nested schemas would violate the SQL spec, as do the numerous
missing features in Postgres. Obviously, they would have to be a sort
of non-conforming extension. It's an opportunity for Postgres to take
the lead and influence the next standard, I guess. Unless the community
decides that it's not worth the hassle, which seems much more likely. I
am curious to know what the "unworkable ambiguities" are. I propose that
if there is any ambiguity at all, just fail the parse and leave it to
the user to write something sensible. Otherwise, it's just a matter of
defining a precise precedence for resolving name scopes, which doesn't
seem very tricky at all.

That is, if a.b is the name of a schema b nested within a schema a, then
a.b.c.d refers to a column d of table c in schema b in schema a. If a is
not the name of a schema, then check to see if it's the name of a database.
If it is, then a.b.c.d has the meaning you define above. If it's not,
then it's an error. The rule is simple: when the identifier has more than
two parts, search for the first part among the schemas first, and then
the catalogs. For the parts after the first and before the last two,
just search the appropriate schemas. As far as I can tell, this syntax
is completely backwards-compatible with existing SQL syntax.

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East, Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129