Thinking about this further I wonder is there a difference between
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY);
and
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY)NOT PERSISTENT;
Since it seems that a locally temporary table is deleted when the connection
that created it closes.
Not even needing a database close contrary to what the documentation states.
Furthermore it does not seem to have any effect on GLOBAL TEMPORARY tables
either.
But only for making persistent tables not persistent (which seems oxymoronic).
Have I misunderstood something?
- Rami
On 04/24/2011 06:33 PM, Rami wrote:
Statement like
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY) TRANSACTIONAL NOT PERSISTENT;
throws a syntax error and the error is identified next to NOT word.
Dropping TRANSACTIONAL away works fine.
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY) NOT PERSISTENT;
Dropping NOT PERSISTENT seems to work fine, too.
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY) TRANSACTIONAL;
But not their combination.
Is TEMP table a GLOBAL or LOCAL TEMPORARY?
Would it make sense also to have a table that is local to the transaction?
Something like
CREATE TRANSIENT TABLE FOO AS (SOME_QUERY);
or maybe
CREATE TRANSIENT TEMPORARY TABLE FOO AS (SOME_QUERY);
to be more aligned with the current syntax.
This kind of table would always be TRANSACTIONAL and NOT PERSISTENT.
Use case:
SELECT
SELECT COUNT(*) FROM TEMP WHERE SOME_FURTHER_RESTRICTION
FROM (SOME_QUERY) AS TEMP
Is not possible because the TEMP table variable created in FROM clause is not
seen by the inner query of the SELECT clause;
This could be written
CREATE TRANSIENT TABLE TEMP AS (SOME_QUERY);
SELECT COUNT(*) FROM TEMP WHERE SOME_FURTHER_RESTRICTION;
Another way to achieve this would be to allow user-defined variables that have
a table variable like this
SET @TEMP = SOME_QUERY;
SELECT COUNT(*) FROM @TEMP WHERE SOME_FURTHER_RESTRICTION;
- Rami
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
