Why is a temporary table not allowed in a stored procedure in Firebird?

Question

I am trying to create a temporary table in a stored procedure in a Firebird database.

List of stored procedures:

SET TERM ^ ; CREATE PROCEDURE initNATIONALHEALTHFUNDS AS BEGIN CREATE GLOBAL TEMPORARY TABLE temp_FUNDS ( NATIONALHEALTHFUNDID Integer NOT NULL, NAME Varchar(128) NOT NULL, CODE Integer NOT NULL ) ON COMMIT PRESERVE ROWS; commit; INSERT INTO tempFUNDS (NATIONALHEALTHFUNDID, CODE, NAME) VALUES ( 01 ,01 , 'Some Foundation'); MERGE INTO NATIONALHEALTHFUNDS AS target USING tempFUNDS AS source ON target.NATIONALHEALTHFUNDID = source.NATIONALHEALTHFUNDID WHEN NOT MATCHED THEN INSERT (NATIONALHEALTHFUNDID, CODE, NAME) VALUES (source.NATIONALHEALTHFUNDID, source.CODE, source.NAME); drop TABLE tempFUNDS; END^ SET TERM ; ^ 

Every time I try to create this procedure, I get an error:

  Engine Code : 335544569 Engine Message : Dynamic SQL Error SQL error code = -104 Token unknown - line 7, column 3 CREATE Total execution time: 0.015s 

What am I doing wrong? Firebird 3.0 rc

Thanks in advance Robert

Answer 1

Firebird does not allow the use of DDL inside stored procedures, so CREATE statements are not allowed in PSQL. As stated in lad2025's answer, you can get around this limitation using EXECUTE STATEMENT .

However, the idea of ​​a global temporary table is that you create it once, and they continue to exist, so you can use them later. Data is visible only to the connection that created the data, and the data is deleted after the transaction is ON COMMIT DELETE ROWS ( ON COMMIT DELETE ROWS ) or the connection is closed ( ON COMMIT PRESERVE ROWS ) depending on the type of global temporary table.

From the language update:

Global temporary tables have persistent metadata, but their contents are transaction-related (default) or connection-related. Each transaction or connection has its own private instance of GTT, isolated from everyone else. Instances are created only when and when the GTT is referenced and destroyed after the transaction is completed or disconnected.

Therefore, instead of trying to create a global temporary table inside your stored procedure, first create it and then create your own stored procedure that uses the already defined GTT.

Answer 2

From the GTT Documentation :

CREATE GLOBAL TEMPORARY TABLES

is a regular DDL statement that is processed by the engine in the same way that the CREATE TABLE statement is processed. Accordingly, it is not possible to create or delete a GTT in a stored procedure or trigger.

You can use Dynamic-SQL and wrap your code with EXECUTE STATEMENT as a workaround:

 SET TERM ^ ; CREATE PROCEDURE initNATIONALHEALTHFUNDS AS BEGIN EXECUTE STATEMENT 'CREATE GLOBAL TEMPORARY TABLE temp_FUNDS ( NATIONALHEALTHFUNDID Integer NOT NULL, NAME Varchar(128) NOT NULL, CODE Integer NOT NULL ) ON COMMIT PRESERVE ROWS; commit;'; ... END^ 

Answer 3

To clarify other correct answers, I use temporary tables mainly for performance problems, for example, when I have a parameterized subset of data that should be a query for a larger set, for example:

 select * from MAIN_TABLE where MAIN_TABLE.ID in (select ID from GTT$IDS) 

where GTT $ IDS is populated with a subset of identifiers.

Sometimes, for very complex procedures, I have to use several temporary tables, so I create them in the metadata (outside the PSQL instructions, of course) as follows:

 create global temporary table GTT$IDS_1 (INT1 integer, INT2 integer); create index IDX_GTT$IDS_11 on GTT$IDS_1 (INT1); create index IDX_GTT$IDS_12 on GTT$IDS_1 (INT2); create global temporary table GTT$IDS_2 ... create global temporary table GTT$IDS_3 ... 

Doing this may be simplified for some advanced SQL developers, but it makes the most sense to me (moving the technique from my dBase / VFP days), and it is very fast compared to a bunch of complex joins.

I really did not find the time to learn how to use the PLAN clause (or make it work correctly), so basically I use this method to create a PLAN through the code when I get slow queries, if that makes sense.