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

Question

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

My stored procedure listing:

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 ; ^

Each time I am trying create this procedure I am getting 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 I am doing wrong? I'm using Firebird 3.0 RC

Teresitateressa

Answer 1

Firebird doesn't allow you to use DDL inside stored procedures, so CREATE statements are disallowed in PSQL. As indicated in the answer by lad2025 you can work around this limitation by using EXECUTE STATEMENT.

However, the idea behind a global temporary table is that you create it once, and they continue to exist so they can be used later. The data is only visible to the connection that created the data, and the data is deleted after transaction commit (ON COMMIT DELETE ROWS) or connection close (ON COMMIT PRESERVE ROWS) depending on the type of global temporary table.

From the Firebird 3.0 Language Reference:

Global temporary tables have persistent metadata, but their contents are transaction-bound (the default) or connection-bound. Every transaction or connection has its own private instance of a GTT, isolated from all the others. Instances are only created if and when the GTT is referenced. They are destroyed when the transaction ends or on disconnection.

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

Gurl

This is the point, after some time struggling about this issue I also think it is the best solution, Thanks ! – Teresitateressa

Answer 2

From GTT documentation:

CREATE GLOBAL TEMPORARY TABLE

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

You can use Dynamic-SQL and wrap your code with EXECUTE STATEMENT as 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^

Schismatic

Thank you for hint, however I am getting error "Dynamic SQL Error SQL error code = -204 Table unknown TEMP_FUNDS". Is there in Firebird alternative to temporary table to keep temporary rows for a short while? – Teresitateressa

@Teresitateressa You need to wrap every statement. Not only creation. Did you do that? – Schismatic

@Teresitateressa Objects used directly in stored procedures need to exist at creation time. Also note that tables created in a transaction cannot be used in DML in that same transaction. So you if you want to use this execute statement solution, then you need to use dynamic SQL using execute statement with autonomous transaction for GTT creation and for the DML. I suggest you don't do that. – Gurl

@lad2025 YEs, I tried wrap every statement, but then I got another error, I gave up to drill down deeper this way, also I could not figure out how to escape literal strings in my insert statement. I chose solution, to create gtt once at the beginning, Anyhow , Thank you a lot ! – Teresitateressa

Answer 3

Just to elaborate on the other above correct answers, I use temporary tables mostly for performance issues, like when I have a parameterized subset of data that needs to be query against a larger set like:

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

where GTT$IDS is populated with the subset of ID's.

Sometimes, for highly complex procedures, I have to use multiple temp tables, so I create them in the metadata (outside of PSQL statements, of course) like so:

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 simplistic for some advanced SQL'ers out there, but it makes the most sense to me (carry over technique from my dBase/VFP days) and it's super fast compared to a bunch of complex joins.

I never really took the time to learn how to use the 'PLAN' clause (or get it working right), so basically I use this technique to generate the PLAN through code when I get slow queries, if that makes sense.

Strobilaceous