Hi,

> Syntax:
>
> CREATE TEMPORARY TABLE <table name> <table contents source>
> [ON COMMIT {PRESERVE | DELETE} ROWS]
>
> Syntax:
> If the optional ON COMMIT clause is omitted, the contents of the temporary

table are discarded at the end of the transaction that

> created them.

According SQL standard :

<table definition> ::=
CREATE [ <table scope> ] TABLE <table name> <table contents source>
[ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
<table element list>
| <typed table clause>
| <as subquery clause>

<table scope> ::= <global or local> TEMPORARY

<global or local> ::=
GLOBAL
| LOCAL

<table commit action> ::=
PRESERVE
| DELETE

<as subquery clause> ::=
[ <left paren> <column name list> <right paren> ] AS <subquery>
<with or without data>

<with or without data> ::=
WITH NO DATA
| WITH DATA

So i'm at least missing the GLOBAL | LOCAL here.

> Semantics:
> An entry for relation <table name> is made in the RDB$RELATIONS system

table. Entries are made in RDB$RELATION_FIELDS and

> RDB$FIELDS relations describing the fields listed in the <table contents

source>, as they would be for a permanent table. The

> entries in RDB$RELATIONS, RDB$FIELDS, and RDB$RELATION_FIELDS are

provisional and unusable until the transaction which issued the

> CREATE statement commits.
>
> All connections can see the definition of temporary tables; temporary

tables are part of the global name space. No connection can

> see data stored in a temporary table by another connection.

A connection "b" can see the table created (and commited) by connection "a",
but can't read or write data in it, correct?

> A temporary table can be either the referenced or referencing

table in a foreign key constraint, but the partner table must

> be a temporary table. If either table is ON DELETE COMMIT, both must be

ON DELETE COMMIT.

So a temporary table cannot make a foreign key to a normal table only to an
other temporary table?

> 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.

> Notes:
> No changes are proposed to ALTER TABLE or DROP TABLE. Changing a table

from permanent to temporary or vice-versa is accomplished

> by dropping the table and recreating it.

> 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.

Should it be possible to make triggers, indexes on this temporary table (i
think so)?

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