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.

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


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.

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.

Implementation:
A temporary table is represented internally by a TemporaryRelation class, which is a subclass of the Relation class. A
TemporaryRelation instance is created either by dfw, CStatement, or during the processing of blr. TemporaryRelation classes hang
off the attachment object as well as the Database object. The TemporaryRelation object provides the location of the index root
page for that table instance and it's pointer page list.

Changes to VIO, DPM, NAV, BTR, and IDX would be primarily cleanup and applying object discipline to the driving structures. Record
source blocks become a class hierarchy. References to the source relation would be through either the TemporaryRelation object or
the Relation.

Data and indexes for a temporary table could be stored on pages in the active database or in a temporary file created for the
instance. Each alternative has advantages.

Using the active database leverages the existing structures. Free space can be located with existing mechanisms completely
unchanged. Page number references in indexes and blobs can be used directly. Cleaning up the pages used by a temporary table in
the case of a system crash is problematic.

Using a temporary file requires that space management and all page references be redirected to the temporary file, increasing the
number of layers that must be aware of this change. A temporary file is automatically destroyed in case of system failure.

Paul Beach
Main Tel (UK):+44 (0) 1844 354357
Mobile: (UK): +44 (0) 7837 898072