Ann,

>>with the limitations above, not much. But I am a little lost as to how
>>the original requirements from Volker Rehn generated this whole
>>discussion about SQL standard temporary tables.

A> That's easy. We set about the produce a design for temporary
A> tables.

That's the mistake I guess. Nobody wants temporary tables as defined
by the SQL standard; people appear to call "temporary tables" what is
actually something different and perhaps doesn't even exist in the
SQL standard. ;-)

A> If we're a serious database builder and claim standard compliance,
A> features are either standard compliant and use standard language
A> with standard semantics, or they are non-standard and use language
A> that is not part of the standard and our own semantics.

Not only I do agree, I'd print it in caps and stick it to one of my
workroom's walls. :-)

>>Scoped table/cursor variables (with table/cursor as a
>>datatype) are what Firebird needs IMHO.

A> OK, that's an interesting discussion.

For me it is, although I'm not sure it is completely IT. Anyway since
you ask here's my humble take at it...

A> What's a scoped cursor variable?

With "scoped" I meant that it can either be global to a database or
local to a PSQL "module" (meaning a trigger, a stored procedure or an
anonymous block - I don't think there are other kinds). Other scopes
(like transaction - but I'd find it difficult to define it) might be
possible. If a multilevel namespace is ever introduced, it could be
used for global cursor/table variables as well.

A> What does it do

it's a "temporary table" ;-) whose structure and data are scoped. By
itself it doesn't do much but it has an identifier (a name) and can be
queried.

A> how is it created

do you mean the type or the instance? The type should be declared with
a create/declare statement, while instances of it are created by
declaring a variable of that type. A good analogy is a domain. A
domain is to a variable what a table data type is to a table variable.
Currently there is no analogy for standard tables, because you define
both the "data type" and the instance of a table with the same create
table statement. Separating the two would open interesting scenarios,
though.

A> how is it destroyed?

A local table is destroyed when it goes out of scope. A global table
is destroyed with a drop statement. This applies to both the type and
the instance(s).

A> Can it be extended, either in content or in definition (e.g. adding
A> columns)?

Again we need to distinguish between the type and the instance. I'd
say the type can't because it generally wouldn't be needed, but altering
the type definition through an alter statement looks coherent (to keep
the domain analogy, we do have alter domain and it is generally
useful). The instance can never be altered, just like an integer
variable cannot be extended to bigint.

A> Can it be preserved?

Does a combination of the thoughts above answer this?

A> How is it different from a scoped table variable?

I used table/cursor to mean "whatever". Dataset would also be an
appropriate name. I prefer table if you ask me.

A> What does it mean for a table to be a datatype?

It means that you can define table columns and procedure parameters of
that type. This gets you "nested tables" and ability to pass
recordsets between procedures, which are both useful things, in
addition to the "fake temporary tables" feature. An instance can also
be used in a from clause.

I believe the concept is part of the relational model from day 0, but
I'm no SQL expert and I can't say whether the SQL standard has
anything similar or equivalent.

Ciao
--
Nando Dessena
http://www.flamerobin.org