Subject Re: create table as select
Author Adam
> I believe the Quell Language used in the INGRESS database some 25 to
> years ago supported temporary
> table creation and it was a big reason, why many users of Quell did not
> like to switch to SQL, the Standard
> promoted by IBM. Temporary tables are a tool used to solve some
> difficult problems, I can easily say that
> over 7 years in programming and maintaining an application using DELPHI
> and Interbase/Firebird, that is the
> one feature that I would find to be the most useful feature that could
> be added. Many new features are icing
> on the cake, but this feature would increase my productivity

You will be pleased then to read:

25 May 2006 Temporary tables Dmitry Yemanov

Implementation of the SQL-2003 compliant global temporary tables has
been committed into the HEAD branch. This feature will be released in
the next (post-v2.0) Firebird version. Design and implementation of
the local temporary tables (both "defined" and "declared", see the SQL
specification for details) are being investigated, but there are no
timeframes defined for that.

> Many times I would like to hold calculated values in a table with
> additional information produced by
> several queries and then sorting the results for display and reports.

I agree in principle that a temporary structure that behaves like a
table could allow the sort of processing formerly reserved for a
client application using a memory based dataset simple inside stored

It is of course already possible to emulate a temporary table using
real tables and including CURRENT_TRANSACTION in the key. You just
need to ensure that your transaction cleans up after itself before it

This however is very different to the create table as select concept,
which appears to be the idea that one should be able to issue a DDL
command to create a new permanent table with an identical structure as
the source table and populate it with the same data. The next obvious
questions are 'Do you copy the constraints/indices/triggers? Should
the PSQL be modified to reflect the new table?'. You don't need too
much imagination to see how it can get really messy.

I agree with Dimitry S though that if you are happy to copy the data
from one table to another identical structure, you may need to
consider whether your design is sound.