On 16/03/2022 00:45, Dmitry Yemanov wrote: > 15.03.2022 21:43, Adriano dos Santos Fernandes wrote: >> >>> In fact, what you priorly define as LT is IMO "declared" LTT. >> >> I had that impression before read the standard, but then I changed my >> opinion. > > "Part 4: Persistent Stored Modules (SQL/PSM)" is about PSQL, AFAIU. > It includes: "12.8 <temporary table declaration>" that refers to "Part > 2: Foundation (SQL/Foundation)" which defines: > > <temporary table declaration> ::= > DECLARE LOCAL TEMPORARY TABLE <table name> <table element list> > [ ON COMMIT <table commit action> ROWS ] > > Given that Part 4 also defines DECLARE CURSOR which is also redirected > to "Part 2: Foundation (SQL/Foundation)" I understand it as declared > LTTs are allowed in PSQL. >
I have part 4 only of SQL 2011. LTTs can be declared only in SQL-server modules, in our case, packages. They cannot be declared inside routines. Sure we may extrapolate, if we do things that we believe is not going to be incompatible with future standards. But standard LTTs seems to be very weird in an aspect: it works per requests. Here is some quotes. 5.b does not copy LTTs identities. 5.g.i should be PSQL routines. That means a package with a LTT cannot store data on a LTT and call another routine of the same package that reads the data. It is much more useful if data is shared in this case. ----- <routine invocation> 5) Preserve the current SQL-session context CSC and create a new SQL-session context RSC derived from CSC as follows: b) The values of the current SQL-session identifier, the SQL-session user identifier, the identities of all instances of global temporary tables, the cursor instance descriptor of all open cursors accessible in the SQL-session, the current constraint mode for each integrity constraint, the current transaction access mode, the current transaction isolation level, the current condition area limit, the subject table restriction flag, and the restricted subject table name list are set to their values in CSC. g) Case: i) If R is an SQL routine, then the identities of all instances of created local temporary tables, declared local temporary tables that are defined by <temporary table declaration>s that are contained in <SQL-client module definition>s, the cursor instance descriptors of all open cursors that are not global extended dynamic cursors, prepared statements that do not have global extended names, and SQL descriptor areas that do not have global extended names are removed from RSC. ii) Otherwise: 1) Remove from RSC the identities of all instances of created local temporary tables that are referenced in <SQL-client module definition>s that are not the <SQL-client module definition> of P, declared local temporary tables that are defined by <temporary table declaration>s that are contained in <SQL-client module definition>s that are not the <SQL- client module definition> of P, and the cursor instance descriptors of all open cursors that are not global extended dynamic cursors and whose SQL-client module is not the SQL- client module of P. 2) It is implementation-defined whether the identities of all instances of created local temporary tables that are referenced in the <SQL-client module definition> of P, declared local tem- porary tables that are defined by <temporary table declaration>s that are contained in the <SQL-client module definition> of P, the cursor instance descriptors of all open cursors that are not global extended dynamic cursors and whose SQL-client module is the SQL- client module of P, prepared statements that do not have global extended names, and SQL descriptor areas that do not have global extended names are removed from RSC. ----- Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
