Subject Re: [firebird-support] create table as select
Author Ann W. Harrison
kapsee wrote:
> It seems there is no support for creating a table as duplicate of
> another by doing "create table foo as select * from bar".

That's a feature that's present in many databases and one we've
discussed at length in the architecture forum. Firebird, as it
presently works, isn't really good at handling applications that
depend on dynamic table creation. The mental model behind its
design is that data is dynamic, but metadata is reasonably stable.

Firebird is designed to handle the evolution of metadata - adding,
dropping, and changing fields, indexes, etc., without stopping the
database or copying tables. But that's not the same as an
application design that creates and drops tables each time the
application runs.

For one thing, tables have internal identifiers that are not reused
and the identifier is only 16 bits. So you get to define a total
of 32639 tables, then you run out of identifiers - the first 129
identifiers are are reserved for the system tables.

There is an implementation of temporary tables in the newest
version of the sources - not in V2 - which will help.

For now, the right answer is to rethink your problem and try to
avoid solutions that depend on creating and dropping tables as
a normal part of application logic. Selectable stored procedures
are a solution. So are permanent tables that hold temporary data,
identified by transaction or connection.