Hi Ann,

> >So i'm at least missing the GLOBAL | LOCAL here.
>
> That's right. GLOBAL or LOCAL govern the visibility of
> temporary tables in SQL-client modules. Since Firebird
> doesn't implement multiple SQL-client modules, we can't
> distinguish between local and global temporary tables.

Ok, but according the standard it isn't a option and one of them should be
spefied. Reading the previous messages in this newsgroup, IMO GLOBAL seems
the best one and LOCAL should not be implemented as you wrote.

> >A connection "b" can see the table created (and commited) by connection

"a",

> >but can't read or write data in it, correct?
>
> Data stored by 'a' is visible only to 'a'. Data stored by 'b'
> is visible only to 'b'. Both 'a' and 'b' see the table definition.

Ok.

> >So a temporary table cannot make a foreign key to a normal table only to

an

> >other temporary table?
>
> That's right, and reasonably important. Without that rule, you
> could - no doubt would - have cross-table relationships that were
> correct for one connection and incorrect for another.

Ok.

> > > System table support:
> > > A new field will be added to RDB$RELATIONS, called RDB$RELATION_TYPE.

If

> >that field value has the value RDB$PERMANENT, the table is
> > > permanent. If the field value is RDB$TEMPORARY_PRESERVE the table is
> >temporary and contents are retained until the connection which
> > > created them terminates. If the value is a RDB$TEMPORARY_DELETE, the
> >table is temporary and contents are discarded at the end of
> > > the transaction which created them.
> >
> >What about RDB$TABLE and RDB$VIEW instead of RDB$PERMANENT or is
> >RDB$RELATION_TRANSACTION_TYPE a better name for it, because on
> >RDB$RELATION_TYPE i think of VIEW or TABLE.
>
> I'm not terribly fussed about what the field is called, or
> even how the information is represented, as long as the RDB$RELATIONS
> entry for a temporary table indicates that it is a temporary table
> and, if so, how records are to be handled on commit.

I even forgotten the EXTERNAL_TABLE type, but yes not that importand at the
moment.

> > > The syntax AS <query expression> has been suggested as an alternative

to

> >the <table contents source> listed in the syntax above. If
> > > that syntax is part of the SQL-99 standard, I can't find it.
> >
> >See snipped above.
>
> > <table contents source> ::=
> > <table element list>
> > | <typed table clause>
> > | <as subquery clause>
> >
> >
> > <as subquery clause> ::=
> > [ <left paren> <column name list> <right paren> ] AS <subquery>
> > <with or without data>

> Ah. I was working from SQL-99, which has subtables but not subqueries.
> It looks like this:

<snip>

I see, SQL-99 is bit old and i look at the latest SQL-spec available.

> >Should it be possible to make triggers, indexes on this temporary table

(i

> >think so)?
>
> Yes.
>
> Regards,
> Ann

A little bit more from the SQL-standard about GLOBAL versus LOCAL :

<quote>
A global temporary table is a named table defined by a <table definition>
that specifies GLOBAL TEMPORARY.
A created local temporary table is a named table defined by a <table
definition> that specifies LOCAL TEMPORARY.
Global and created local temporary tables are effectively materialized only
when referenced in an
SQL-session. Every SQL-client module in every SQL-session that references a
created local temporary table
causes a distinct instance of that created local temporary table to be
materialized. That is, the contents of a
global temporary table or a created local temporary table cannot be shared
between SQL-sessions.

In addition, the contents of a created local temporary table cannot be
shared between SQL-client modules of a
single SQL-session. The definition of a global temporary table or a created
local temporary table appears in a
schema. In SQL language, the name and the scope of the name of a global
temporary table or a created local
temporary table are indistinguishable from those of a persistent base table.
However, because global temporary
table contents are distinct within SQL-sessions, and created local temporary
tables are distinct within SQLclient
modules within SQL-sessions, the effective <schema name> of the schema in
which the global temporary
table or the created local temporary table is instantiated is an
implementation-dependent <schema name> that
may be thought of as having been effectively derived from the <schema name>
of the schema in which the
global temporary table or created local temporary table is defined and the
implementation-dependent SQLsession
identifier associated with the SQL-session.

In addition, the effective <schema name> of the schema in which the created
local temporary table is instantiated
may be thought of as being further qualified by a unique
implementation-dependent name associated with the
SQL-client module in which the created local temporary table is referenced.

A module local temporary table is a named table defined by a <temporary
table declaration> in an SQL-client
module. A module local temporary table is effectively materialized the first
time it is referenced in an SQLsession,
and it persists for that SQL-session.

A declared local temporary table may be declared in an SQL-client module.

A declared local temporary table is a module local temporary table. A
declared local temporary table is accessible
only by externally-invoked procedures in the SQL-client module that contains
the <temporary table declaration>
that declares the declared local temporary table. The effective <schema
name> of the <schema qualified
name> of the declared local temporary table may be thought of as the
implementation-dependent SQLsession
identifier associated with the SQL-session and a unique
implementation-dependent name associated
with the <SQL-client module definition> that contains the <temporary table
declaration>.

All references to a declared local temporary table are prefixed by "MODULE."
.

The materialization of a temporary table does not persist beyond the end of
the SQL-session in which the table
was materialized. Temporary tables are effectively empty at the start of an
SQL-session.
</quote>


ans about the as subquery clasue :


<quote as subquery clause>
6) If <as subquery clause> is specified, then:
a) Let ASQ be the <subquery> immediately contained in <as subquery
clause>. Let QT be the
table specified by ASQ.
b) If any two columns in QT have equivalent <column name>s, or if any
column of QT has an
implementation-dependent name, then <column name list> shall be
specified.
c) Let D be the degree of QT.
d) <column name list> shall not contain two or more equivalent <column
name>s.
e) The number of <column name>s in <column name list> shall be D.
f) For i, 1 (one) ? i ? D:
i) Case:
1) If <column name list> is specified, then let QCNi be the i-th
<column name> in
that <column name list>.
2) Otherwise, let QCNi be the <column name> of the i-th column of
QT.
ii) Let QDTi be the declared type of the i-th column of QT.
iii) If the nullability characteristic of the i-th column of QT is
known not nullable, then
let QNCi be NOT NULL; otherwise, let QNCi be the zero-length
string.
iv) Let CDi be the <column definition> QCNi QDTi QNCi
g) <as subquery clause> is effectively replaced by a <table element
list> TEL of
the form: CD1, ..., CDD
</quote>


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81