"Ann W. Harrison" <aharrison@...> wrote:

>
> Proposal: add a new, non-standard SQL construct that functions like a
> table and is specific to a SQL session. This object is called, for lack
> of a better term, a transient data set.

First question, whether it defines a query or a data set? I.e. whether all references are passed to the actual relations (like a view behaves) or it defines some materialized data?

If this is a query, then it looks like SQL-99 common table expressions (aka WITH clause). But CTE don't allow modifications, AFAIK. So, if you insist on INSERT/UPDATE/DELETE, then we have something like a temporary view.

If this is an actual data set, then how does it differ from LTTs?

> A transient data set is created with a SQL select statement that
> includes a new keyword YIELDING. For a singleton select or an
> interactive select, YIELDING is an alternative to INTO.

So we have a result assigned to some "table variable" which can be referenced later? Sounds like a real data set.

> The columns of transient data set correspond in name and data type with
> the elements in the select list of the statement that created the set.
> No other attributes of the elements of the source data are assigned to
> the transient data set columns.

Fine.

> A transient data set is destroyed when the session ends or with the drop
> transient data set statement: DROP TRANSIENT DATA SET <name>

Equal to DROP TABLE for a LTT.

> Projection:
> SELECT a, c FROM my_data YIELDING my_data;
>
> Redefinition:
> SELECT x.a, y.b, x.c
> FROM table1 x LEFT OUTER JOIN my_data y ON y.a = x.a
> YIELDING my_data;
>
> Combination
> SELECT x.a, x.b, x.c, x.d FROM table2 x
> UNION
> SELECT y.a, y.b, y.c, NULL FROM my_data y
> YIELDING my_data;
>

Fine. RECREATE LTT doesn't allow projections.

> The non-standard DDL statements CREATE INDEX and DROP INDEX can also be
> used on transient data sets. The indexes created are destroyed
> automatically when the transient data set is destroyed.

The major question then. Where are the data (both rows and index b-tree) stored? Are blobs supported? And again, what is the actual difference between your proposal and already discussed LTTs (considering the suggested CREATE AS SELECT extention)?


Dmitry