On 15/03/2022 14:02, Vlad Khorsun wrote:
>
>> 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.
>
> Could you specify, in short, what part of standard is hard to apply to
> the Firebird ?
>
Declared LTTs are defined in "SQL-client module"
SQL-client module seems to have a relation to packages.
They can group procedures.
So as I said, declared LTTs feat well in packages.
The standard does not define LTTs inside routines.
If they did, they could have defined it with different semantics than
what I propose, for example, it could use the routine only as a scope,
but with shared data between invocations. So my reluctance in define LTT
inside routines.
>> Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE <name> ... [ ON
>> COMMIT PRESERVE/DELETE ROWS ].
>
> "ON COMMIT" clause seems meaningless for LTT's DECLARED within PSQL
> routine,
Does it means ON COMMIT would be prohibited there inside routines?
> while could be useful for packaged LTT's.
>
But LT (non-transactional) is also useful in packages.
It then means in package not using ON COMMIT would mean a different
thing than absence of ON COMMIT in GTT. Not something we would want.
>> 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.
>
> Mostly agree - just not sure about metadata, it could be discussed later.
>
Sure.
>> LTs could also be added in packages. It's different feature than LTTs.
>
> What is a difference ?
>
In that scope, mostly about it not being transactional.
>> I propose syntax of LT:
>>
>> DECLARE LOCAL TABLE <name> (
>> { <column definition> }...
>> );
>
> This statement should be used as part of common DECLARE section of
> PSQL routine
Yes.
>> "DELETE FROM <local table>" would be optimized to use
>> blr_local_table_truncate.
>
> You mean - if without WHERE clause, correct ?
Sure.
> What about undo ?
> In general - how operations on LT[T] should be handled in case of PSQL
> exceptions ?
>
It will not have undo.
PSQL exceptions during multi-row changes would abort the operation
leaving already done changes.
>> 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.
>
> Also, packaged LT should hide "GLOBAL" name when used in routine of
> the same
> package, correct ?
>
Yes. But note that I propose packaged LTs for a later moment. We would
first need more useful and simple feature first implemented there:
packaged variables.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
