Re: Temporary Views

On Wed, 2002-08-14 at 08:32, Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > SQL99 is pretty clear about temporary tables, at least.
> 
> It is ... and in fact the spec's notion of a temp table has nearly
> nothing to do with ours.  They contemplate a temp table as an abstract
> table schema, if you will, that gets instantiated locally within a
> session upon first use.  There is no ability in the spec for two
> sessions to create unrelated temp tables of the same name --- their temp
> tables of the same name must share the same, predefined schema.

What I see is that the (local) TEMP table should be indistinguishable
from persistent table at the SQL level, but visible only in the module
that creates it. So it seems to say that TEMP tables should not
_visibly_ be in separate schemas from the rest of tables, which again
seems to imply that you should be able to reference them with
<schema>.<table> .

> I wasn't around when PG's temp table concept was created, but I think
> it's considerably superior to the spec's concept.

It seems much like the spec's concept of LOCAL TEMP, with the exception
that they are visible everywhere, not only in defining "module"
(whatever a module is ;)

> I'm willing to compare the spec's notions for guidance, but we must not
> take it as gospel when we're deciding how temp objects should behave.

I just brought it up for your hypothetical case when temp tables needed
to be referenced by full <schema>.<table> syntax. I don't think it will
really be an issue.

> Their concept of temp-ness is different and very much more limited.

Not sure about the "more limited" part. I really can't see good reason
for having two tables with the same name but different structure.

Not needing to define the temp table in each and every session may seem
desirable for some people.

Perhaps we need to get some description of differences from the spec
into our docs. Currently there seems to be _no_ mention of temp tables
at all, at least in Users Guide.

----------
Hannu