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

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.

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^