Re: [Firebird-devel] Local tables
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 " that refers to "Part 2: Foundation (SQL/Foundation)" which defines: ::= DECLARE LOCAL TEMPORARY TABLE [ ON COMMIT 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 also did not found any major DBMS that implemented declared LTTs, nor SQL-standard modules. Sybase ASA, as you already mentioned, although maybe not so "major". From their docs: "Declared local temporary tables within compound statements exist within the compound statement." Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 15:20, Vlad Khorsun wrote: >> So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? > > At first look I see two way's to go: > a) PSQL routines can't see CREATE'd LTT's, or > b) PSQL routines can see and use LTT definition that exists at the > moment of routine > definition\loading into metadata cache. Such LTT's definition should > be not changed > while dependend PSQL routine reside in metadata cache, or PSQL > routine should be > invalidated when LTT definition it depends on is changed. > > Of course, there could be something else, lets continue to think on it. > I think (a) is the best option. (b) seems not friendly for Firebird and for this usage GTT seems the way to go. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 15:00, Adriano dos Santos Fernandes wrote: >> Would it be possible to use some other word to distinguish, instead of >> just leaving out "local" (which essentially loses the most important >> aspect of what it really is)? >> >> Suggestions for thought: >> >> Internal? >> Volatile? >> Virtual? >> Transient? >> > > None of these words seems good for me to distinguish the difference. > I liked Sybase syntax: http://dev.cs.ovgu.de/db/sybase9/help/dbrfen9/0356.htm declare local temporary table ( ... ) [ ON COMMIT { DELETE | PRESERVE } ROWS | NOT TRANSACTIONAL ] Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:07, Dmitry Yemanov wrote: > 15.03.2022 17:14, Adriano dos Santos Fernandes wrote: >> >> 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. > > What about autonomous transactions? Will procedure's LT data be visible > for them or every autonomous transaction gets its own copy? > Like a standard variable, data will be shared with the autonomous transaction scope. Note that if we say that as the contrary, LT would be very similar to a LTT (with transaction control). And that would not be problematic, but... I also want packaged LT in the future, with would be very useful to work outside transaction control. >> 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. > > I'd just adapt their definitions to our realities. For me, "created" LTT > is similar to GTT (i.e. stored in the schema) but with data isolated per > request (per PSQL routine). My understanding is that it is per session / package (SQL-client module), not request / routine. > "Declared" LTTs are defined inside the PSQL routine, Inside a package (SQL-client module) with data shared in the package / session. > 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. I also did not found any major DBMS that implemented declared LTTs, nor SQL-standard modules. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 13:00, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 15.03.2022 15:37: >> On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: >>> Very interesting - but other name for them should be used. It's too easy >>> to loose difference between LT & LTT that are absolutely different >>> things. >>> >> Maybe just "DECLARE TABLE" then? > > Oracle uses term "collection". Why not to adapt their syntax? > The only similarity with LT is that data is not under transaction control. Oracle's collection variables are not integrated with SQL update commands. It's mostly a PL/SQL (not SQL) feature. LT, while defined in PSQL, works like a SQL table, except that it does not uses transaction control. Oracle's collection is integrated with standard tables. It's part of their object-relational integration. Collections only as variable feels as an incomplete feature for me. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:56, Adriano dos Santos Fernandes wrote: On 15/03/2022 14:39, Vlad Khorsun wrote: 15.03.2022 19:27, Dmitry Yemanov wrote: 15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). First, CREATE'd LTT's have the visibility scope and lifetime of the attachment. Its definition is not seen by other attachments and every attachment could have LTT with the same name and different definition. Second, attachment could have private part (instance) of metadata cache that will contains definitions of every CREATE'd LTT and every statement could use it without additional penalty. No need to pollute persistent schema with temporary objects and pay runtime cost for storing\erasing such definitions. So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? At first look I see two way's to go: a) PSQL routines can't see CREATE'd LTT's, or b) PSQL routines can see and use LTT definition that exists at the moment of routine definition\loading into metadata cache. Such LTT's definition should be not changed while dependend PSQL routine reside in metadata cache, or PSQL routine should be invalidated when LTT definition it depends on is changed. Of course, there could be something else, lets continue to think on it. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
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 ... [ 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 ( >> { }... >> ); > > This statement should be used as part of common DECLARE section of > PSQL routine Yes. >> "DELETE FROM " 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
Re: [Firebird-devel] Local tables
Vlad Khorsun wrote 15.03.2022 18:39: No need to pollute persistent schema with temporary objects and pay runtime cost for storing\erasing such definitions. That's a good occasion for creating a virtual metadata dictionary that provides records for persistent objects as well as session-local ones. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 12:03, Kjell Rilbe wrote: > Den 2022-03-15 kl. 15:37, skrev Adriano dos Santos Fernandes: >> On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: >>> Very interesting - but other name for them should be used. It's too easy >>> to loose difference between LT & LTT that are absolutely different >>> things. >>> >> Maybe just "DECLARE TABLE" then? >> >> We would refer to them as "declared tables". >> >> In routines they will be local declared tables. >> >> In the future, when they could also be in a package, a packaged declared >> table. > > From a linguistic point of view I find "declared" to be a nonsense > qualification of "table". Aren't all tables declared in a sense? > No, some are CREATEd. > The essence of these tables seems to be that they are local to > "something", but the term "local" seems to be "taken" by the things > called local temporary tables(?). > Yes. > Would it be possible to use some other word to distinguish, instead of > just leaving out "local" (which essentially loses the most important > aspect of what it really is)? > > Suggestions for thought: > > Internal? > Volatile? > Virtual? > Transient? > None of these words seems good for me to distinguish the difference. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:39, Vlad Khorsun wrote: > 15.03.2022 19:27, Dmitry Yemanov wrote: >> 15.03.2022 20:17, Vlad Khorsun wrote: >>> For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). >>> >>> I'd consider about CREATE'd LTT as attachment-private object. I see >>> no need to store its definition at the persistent schema. >> >> If multiple procedures process the same layout of temporary data, IMHO >> it's handier to create such LTT definition once (as persistent) rather >> than declare the same LTT in the every procedure (or attachment). > > First, CREATE'd LTT's have the visibility scope and lifetime of the > attachment. > Its definition is not seen by other attachments and every attachment > could have > LTT with the same name and different definition. Second, attachment > could have > private part (instance) of metadata cache that will contains definitions > of every > CREATE'd LTT and every statement could use it without additional penalty. > > No need to pollute persistent schema with temporary objects and pay > runtime > cost for storing\erasing such definitions. > So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:31, Alex Peshkoff via Firebird-devel wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). Same for almost all objects declared in procedure that are more complex than INT. For example cursor definition may be rather complex and also usable in multiple procedures. True. But I don't remember CREATE CURSOR in the SQL spec ;-) Anyway, IMO "declared" LTTs are much more useful than "created" LTTs, so the latter ones may be deferred. Or we may live without them at all ;-) Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:27, Dmitry Yemanov wrote: 15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). First, CREATE'd LTT's have the visibility scope and lifetime of the attachment. Its definition is not seen by other attachments and every attachment could have LTT with the same name and different definition. Second, attachment could have private part (instance) of metadata cache that will contains definitions of every CREATE'd LTT and every statement could use it without additional penalty. No need to pollute persistent schema with temporary objects and pay runtime cost for storing\erasing such definitions. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 3/15/22 20:27, Dmitry Yemanov wrote: 15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). Same for almost all objects declared in procedure that are more complex than INT. For example cursor definition may be rather complex and also usable in multiple procedures. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:07, Dmitry Yemanov 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. I'd just adapt their definitions to our realities. +1 For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. It would be good to have ability to query schema for such objects, though. "Declared" LTTs are defined inside the PSQL routine, with data isolated the same way. In fact, what you priorly define as LT is IMO "declared" LTT. +1. And don't forget about packages. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 17:14, Adriano dos Santos Fernandes wrote: 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. What about autonomous transactions? Will procedure's LT data be visible for them or every autonomous transaction gets its own copy? 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. I'd just adapt their definitions to our realities. For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). "Declared" LTTs are defined inside the PSQL routine, with data isolated the same way. In fact, what you priorly define as LT is IMO "declared" LTT. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 18:00, Dimitry Sibiryakov wrote: Adriano dos Santos Fernandes wrote 15.03.2022 15:37: On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Maybe just "DECLARE TABLE" then? Oracle uses term "collection". Why not to adapt their syntax? Is it standard ? Could "collection" be joined with regular tables ? Isn't it is better to implement standard SET\ARRAY ? If there is strong demand to have Oracle's collection - could it be implemented later based on LT[T] ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 16:14, Adriano dos Santos Fernandes wrote: 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. Ok. 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 uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON COMMIT PRESERVE/DELETE ROWS ]. "ON COMMIT" clause seems meaningless for LTT's DECLARED within PSQL routine, while could be useful for packaged LTT's. 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. LTs could also be added in packages. It's different feature than LTTs. What is a difference ? I propose syntax of LT: DECLARE LOCAL TABLE ( { }... ); This statement should be used as part of common DECLARE section of PSQL routine or any place of package definition (but before usage), correct ? It will work with common commands: DELETE, UPDATE, INSERT, MERGE, UPDATE OR INSERT, SELECT. "DELETE FROM " would be optimized to use blr_local_table_truncate. You mean - if without WHERE clause, correct ? What about undo ? In general - how operations on LT[T] should be handled in case of PSQL exceptions ? 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 ? 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. I'd say indices (at least one) is a must. Probably we could allow only inplace index definition (i.e. as part of DECLARE LOCAL TABLE only). But, of course, it could be postponed for a while. So far I see no need to introduce non-standard LT and would like to see LTT's. But I, probably, not see whole picture. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 18:03, Kjell Rilbe wrote: From a linguistic point of view I find "declared" to be a nonsense qualification of "table". Aren't all tables declared in a sense? Maybe, but this is what SQL spec suggests. In PSQL, we have any locals syntactically "declared" -- DECLARE VARIABLE, DECLARE CURSOR. So it looks consistent to have DECLARE LOCAL TEMPORARY TABLE there too. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
Adriano dos Santos Fernandes wrote 15.03.2022 15:37: On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Maybe just "DECLARE TABLE" then? Oracle uses term "collection". Why not to adapt their syntax? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
Den 2022-03-15 kl. 15:37, skrev Adriano dos Santos Fernandes: On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Maybe just "DECLARE TABLE" then? We would refer to them as "declared tables". In routines they will be local declared tables. In the future, when they could also be in a package, a packaged declared table. From a linguistic point of view I find "declared" to be a nonsense qualification of "table". Aren't all tables declared in a sense? The essence of these tables seems to be that they are local to "something", but the term "local" seems to be "taken" by the things called local temporary tables(?). Would it be possible to use some other word to distinguish, instead of just leaving out "local" (which essentially loses the most important aspect of what it really is)? Suggestions for thought: Internal? Volatile? Virtual? Transient? Regards, Kjell begin:vcard fn:Kjell Rilbe n:Rilbe;Kjell org:Marknadsinformation i Sverige AB;Utveckling & databaser adr;quoted-printable:;;Ulvsundav=C3=A4gen 106C;Bromma;Stockholm;16867;Sverige email;internet:kjell.ri...@marknadsinformation.se title:Utvecklings- & databasansvarig tel;cell:0733-442464 x-mozilla-html:TRUE url:http://www.marknadsinformation.se version:2.1 end:vcard Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: > > Very interesting - but other name for them should be used. It's too easy > to loose difference between LT & LTT that are absolutely different things. > Maybe just "DECLARE TABLE" then? We would refer to them as "declared tables". In routines they will be local declared tables. In the future, when they could also be in a package, a packaged declared table. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 3/15/22 17:14, Adriano dos Santos Fernandes wrote: 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. Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Local tables
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 ... [ 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 ( { }... ); It will work with common commands: DELETE, UPDATE, INSERT, MERGE, UPDATE OR INSERT, SELECT. "DELETE FROM " 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