Hi Ann,

> Before we go into the specifics, can someone tell me
> if the <as subquery clause> is specific to temporary
> table definitions, or can it apply to permanent tables
> as well? From the snippets I've seen, it appears to
> apply to both table types.

Yes, it can be applied to permanent and temporary tables.

> If the clause is general, perhaps we can separate the
> discussion implementation of the <as subquery clause>
> from the temporary table implementation.

I agree, this is a separate discussion.

<snip>

> So, the <as subquery clause> works pretty much exactly as one
> would expect. Does it create a dependency between the source
> table and the newly defined table? For example, consider a
> table like this:
>
> create table foo as select * from bar;
>
> What happens when I add a column to bar? When I drop bar
> completely? Does that change if the table is temporary?

In my understanding the "inherited definition" is only taken at definition
time and what happends after that is not importand.

> Now add in the <with or without data> qualifier
> <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
>
> If you've got a permanent table, with data, you probably
> don't want to have to delete that table when you change the
> source.

"Just" copy the data after the definition is done from the subquery if "WITH
DATA" is specified.
There's nothing to delete.

> If you've got a temporary table with data, you
> probably want the table definition to follow the source,
> since you're going to be filling it with data when the
> it's reference by a connection and it's nice to have the
> query continue to work.
>
> How's that supposed to happen?

You meant, there could be a change in the table between the "definition"
copy and the "data" copy?
I guess we just see the normal error we'll see if we're doing it in steps
"by hand".

1) CREATE TABLE TABLEY AS SELECT * FROM TABLEX => as subquery BLR
2) TABLY definition done
3) column x dropped from TABLEX
4) as subquery BLR executed >= error, because column x doesn't exists
anymore.

Do-able?

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