Hi!
As part of "Support multiple rows for DML RETURNING (#6815)" feature,
BLR verbs for "local table" were created.
Local tables (LT) as defined there works outside transaction control.
For #6815 this does not matter, but I want to add LT feature that will
use these verbs.
LTs are defined inside PSQL routines and its data is separated per
routine invocation. For example a recursive routine will have different
data in its LTs per each invocation, like local variables.
SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be
"created" or "declared". The semantics of these standard specs are
different and not very easy applicable to Firebird as they are based on
standard notion of modules, that is not similar to PSQL routines.
Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE <name> ... [ ON
COMMIT PRESERVE/DELETE ROWS ].
Declared LTTs could be done as part of packages. They would be like
GLOBAL TEMPORARY TABLES (GTT) but declared (created, updated) as part of
packages headers (public) or body (private). They would work under
transaction control and may be represented in metadata.
LTs could also be added in packages. It's different feature than LTTs.
I propose syntax of LT:
DECLARE LOCAL TABLE <name> (
{ <column definition> }...
);
It will work with common commands: DELETE, UPDATE, INSERT, MERGE, UPDATE
OR INSERT, SELECT.
"DELETE FROM <local table>" would be optimized to use
blr_local_table_truncate.
Standard scope rules will be used. A LT may use the same name of a
schema-based table and will shadow it inside the routine.
Future enhancements may be done (index usage, faster get of count of
records), but I don't want to go there in initial design/implementation.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
