At 01:38 PM 11/23/2004, Arno Brinkman wrote:

>So i'm at least missing the GLOBAL | LOCAL here.

That's right. GLOBAL or LOCAL govern the visibility of
temporary tables in SQL-client modules. Since Firebird
doesn't implement multiple SQL-client modules, we can't
distinguish between local and global temporary tables.

>A connection "b" can see the table created (and commited) by connection "a",
>but can't read or write data in it, correct?

Data stored by 'a' is visible only to 'a'. Data stored by 'b'
is visible only to 'b'. Both 'a' and 'b' see the table definition.

>So a temporary table cannot make a foreign key to a normal table only to an
>other temporary table?

That's right, and reasonably important. Without that rule, you
could - no doubt would - have cross-table relationships that were
correct for one connection and incorrect for another.

> > System table support:
> > A new field will be added to RDB$RELATIONS, called RDB$RELATION_TYPE. If
>that field value has the value RDB$PERMANENT, the table is
> > permanent. If the field value is RDB$TEMPORARY_PRESERVE the table is
>temporary and contents are retained until the connection which
> > created them terminates. If the value is a RDB$TEMPORARY_DELETE, the
>table is temporary and contents are discarded at the end of
> > the transaction which created them.
>
>What about RDB$TABLE and RDB$VIEW instead of RDB$PERMANENT or is
>RDB$RELATION_TRANSACTION_TYPE a better name for it, because on
>RDB$RELATION_TYPE i think of VIEW or TABLE.

I'm not terribly fussed about what the field is called, or
even how the information is represented, as long as the RDB$RELATIONS
entry for a temporary table indicates that it is a temporary table
and, if so, how records are to be handled on commit.

> > The syntax AS <query expression> has been suggested as an alternative to
>the <table contents source> listed in the syntax above. If
> > that syntax is part of the SQL-99 standard, I can't find it.
>
>See snipped above.

> <table contents source> ::=
> <table element list>
> | <typed table clause>
> | <as subquery clause>
>
>
> <as subquery clause> ::=
> [ <left paren> <column name list> <right paren> ] AS <subquery>
> <with or without data>

Ah. I was working from SQL-99, which has subtables but not subqueries.
It looks like this:

11.3 <table definition>
Function
Define a persistent base table, a created local temporary table,
or a global temporary table.
Format
<table definition> ::=
CREATE [ <table scope> ] TABLE <table name>
<table contents source>
[ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
<table element list>
| OF <user-defined type>
[ <subtable clause> ]
[ <table element list> ]

<table scope> ::=
<global or local> TEMPORARY

<global or local> ::=
GLOBAL
| LOCAL

<table commit action> ::=
PRESERVE
| DELETE

<table element list> ::=
<left paren> <table element> [ { <comma> <table element>
}... ] <right paren>

<table element> ::=
<column definition>
| <table constraint definition>
| <like clause>
| <self-referencing column specification>
| <column options>

<self-referencing column specification> ::=
REF IS <self-referencing column name> <reference generation>

<reference generation> ::=
SYSTEM GENERATED
| USER GENERATED
| DERIVED

<self-referencing column name> ::= <column name>

<column options> ::=
<column name> WITH OPTIONS <column option list>

<column option list> ::=
[ <scope clause> ]
[ <default clause> ]
[ <column constraint definition>... ]
[ <collate clause> ]

<subtable clause> ::=
UNDER <supertable clause>

<supertable clause> ::= <supertable name>

<supertable name> ::= <table name>

<like clause> ::= LIKE <table name>

>Should it be possible to make triggers, indexes on this temporary table (i
>think so)?

Yes.

Regards,


Ann