Re: [HACKERS] Creating temp tables inside read only transactions

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> If for some reason we needed to have tables that happened to be called
>> x.y.z and a.b.c accessible from a single SQL session, we could allow
>> that much more simply by allowing schemas to be nested. Then we could
>> allow arbitrary numbers of levels, not just three.
>
> FWIW, I actually tried to do that back when we first introduced schema
> support (the fact that the code calls them namespaces and not schemas
> is a leftover from that idea). It turns out to be a whole lot harder
> than it sounds, because of the ambiguity you get about which name goes
> at what level. A simple example of this is: if you write "x.y" in a
> query, is that meant to be table x's column y, or is it meant to be
> field y within a composite column x of some table in the query?
> We've resolved that by requiring you to write "(x).y" when you mean
> the latter, but it's not exactly an intuitive or pleasant answer.
> In the same way, if namespaces can be nested to different levels,
> it gets really messy to support abbreviations of any sort --- but
> the SQL spec requires us to be able to do so.

What if you used the context of the calling code and resolve in favor of
whatever match is closest to it? The problem is related to general-purpose
programming languages.

Basically start looking in the lexical context for an "x" and if you find one
use that; otherwise, assuming we're talking about referencing code that lives in
the database such as a function, look at the innermost schema containing the
referencing code and see if it has a direct child named "x"; otherwise go up one
level to a parent schema, and so on until you get to the top, and finding none
by then say it doesn't exist.

If there are several "x" in this search sequence, only use the first one
regardless of whether it has a "y", so to prevent bugs from too much complexity.
Same for just looking for "x" by itself in fact, not just an "x.y".

For the case of calling code that doesn't live in the database such as a
client-side query, I believe there are session variables like "current schema"
or such, and you can use this as the starting point for the search for "x",
looking first at what that schema directly contains, and then its parent, and so on.

Something like that.

Or ignore what I said about starting in a lexical context and do what you
already do there, but keep what I said about relative order of schemas to
search, only searching direct children of ancestors of the current code's
context schema starting with the current context.

You could also come up with some "relative name" syntax such as filesystems
support with their ../ and such, but that's further from standard SQL.

-- Darren Duncan