Emulation of temporary tables in Firebird

Copyrights (c), Vladimir Karataev, 2003. Translated by Marina Novikova

When migrating from file databases or from MS SQL and ORACLE to Firebird, many developers often inquire about temporary tables. They are accustomed to using them while writing stored procedures. Though Firebird lacks temporary tables, a number of its mechanisms successfully emulate them. Below you will read about three methods of such emulation.

The first method. In most cases you can simply use the following structure: FOR SELECT INTO DO . The cursor-based loop FOR SELECT is used to execute operations (DO) on variables (INTO), which value is set equal to those of returned query fields (SELECT). And the Firebird interpreter will:

1. execute the query creating _a kind of_ a temporary table in RAM (or in a temporary file if the whole table is too big for RAM).

2. go to the first record of the Select query.

3. set field values of the first query record to SP variables, mentioned in "into" according to their order: =,=, etc.

4. execute operations mentioned in the block DO.

5. go the the second query record (if it exists) and repeat the same operations from step 3.

Full syntax of this operator is available in manuals (see for example "LANGREF.PDF"). To some extent FOR SELECT is a temporary table and all the operations are done on it. Now I will illustrate this with a table:

// personal accounting
CREATE TABLE CASH (
   ID INTEGER, // the record identifier in the table
   NAME VARCHAR(30), // expenses/income
   MONEY INTEGER, // the sum in dollars: with "plus" income (for example, wages), 
   //with "minus" expenses (for example, purchase)*
   RDATE TIMESTAMP // the date of record listing);
 

And you need to make an annual report on obtained and gone sums in such a form:

The point is that you have to divide the column MONEY into two and count the total sum. Roughly you can:

1. do a query on income: SELECT NAME, MONEY FROM CASH WHERE MONEY>=0.
2. then do a query on expenses: SELECT NAME, MONEY FROM CASH WHERE MONEY<0.
3. count total income: SELECT SUM(MONEY) FROM CASH WHERE MONEY>=0.
4. and total expenses: SELECT SUM(MONEY) FROM CASH WHERE MONEY<0.
5. join the obtained results of these queries into a table in the client application.

To do this you needed altogether five queries. Temporary queries might be more effective solution. Using Firebird you will have:

CREATE PROCEDURE RESULT (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
   RETURNS (
   NAME VARCHAR(30),
   INCOME INTEGER,
   EXPENSES INTEGER)
AS
   DECLARE VARIABLE SINCOME INTEGER;
   DECLARE VARIABLE SEXPENSES INTEGER;
   DECLARE VARIABLE TMONEY INTEGER;
BEGIN
   /* Initialization of variables */
   SINCOME=0;
   SEXPENSES=0;
   /* Selection */
   FOR SELECT NAME, MONEY FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD
   INTO :NAME, :TMONEY
 	DO BEGIN
		/* Division of the MONEY field into two columns - INCOME (income)    and EXPENSES (expenses) */
		IF (TMONEY>=0) THEN BEGIN
			INCOME=TMONEY;
			EXPENSES=0;
			/* Calculation of the total income sum */
			SINCOME=SINCOME+TMONEY;
		END
		ELSE BEGIN
			EXPENSES=TMONEY;
			INCOME=0;
			/* Calculation of the total expenses sum */
			SEXPENSES=SEXPENSES+TMONEY;
		END
		SUSPEND;
   END
   /* Showing the total sum */
   NAME='TOTAL';
   INCOME=SINCOME;
   EXPENSES=SEXPENSES;
   SUSPEND;
END

The second method enables you to use FOR SELECT and keep previous values of table fields in SP variables. In the previous example the item "wages" will be shown as many times as it is mentioned in the table CASH. So if the person has got his/her wages 12 times a year, this value will be shown 12 times (and the list won't be sorted). If you group all similar expense items and sum up corresponding columns, our table will be more readable. Then to sum up items you need to have sorting with record accumulation. For this you need to compare each item name in the current record with that of the previous one and keep intermediate results of the item calculation:

This will look as:

CREATE PROCEDURE RESULT2 (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
   RETURNS (
   NAME VARCHAR(30),
   INCOME INTEGER,
   EXPENSES INTEGER)
AS
   DECLARE VARIABLE SINCOME INTEGER;
   DECLARE VARIABLE SEXPENSES INTEGER;
   DECLARE VARIABLE TMONEY INTEGER;
   DECLARE VARIABLE STDOH INTEGER;
   DECLARE VARIABLE STRAS INTEGER;
   DECLARE VARIABLE STOLD VARCHAR(30) CHARACTER SET WIN1251;
   DECLARE VARIABLE STNEW VARCHAR(30) CHARACTER SET WIN1251;
BEGIN
   /* Initilization of variables */
   SINCOME=0;
   SEXPENSES=0;
   STDOH=0;
   STRAS=0;
   STOLD='';
   STNEW='';
   /* Selection */
   FOR SELECT NAME, MONEY FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD ORDER BY    NAME
   INTO :STNEW, :TMONEY
   DO BEGIN
		/* Detection of the beginning of a new item */
		IF (:STOLD<>:STNEW) THEN BEGIN
			/* showing of the result by the previous record */
			NAME=STOLD;
			INCOME=STDOH;
			EXPENSES=STRAS;
			IF (:NAME<>'') THEN SUSPEND;
			/* setting variables to zero to sum up by the next record */
			STOLD=STNEW;
			STDOH=0;
			STRAS=0;
		END
		/* check if the summation is carried on by the same record    */
		IF (:STOLD=:STNEW) THEN BEGIN
			/* division of income and expenses into columns and summing them up */
			IF (:TMONEY>=0) THEN BEGIN
				/* calculation of income in the current record */
				STDOH=STDOH+TMONEY;
				/* calculation of the total income */
				SINCOME=SINCOME+TMONEY;
			END
			ELSE BEGIN
				/* calculation of expenses in the current record */
				STRAS=STRAS+TMONEY;
				/* calculation of the total expenses */
				SEXPENSES=SEXPENSES+TMONEY;
			END
   	END
	END
   /* it is necessary to count the last record of the list */
   NAME=STNEW;
   INCOME=STDOH;
   EXPENSES=STRAS;
   SUSPEND;
   /* showing of the total sum */
   NAME='TOTAL';
   INCOME=SINCOME;
   EXPENSES=SEXPENSES;
   SUSPEND;
END

There is a particular case of the first two methods - an ability to use the structure FOR SELECT INTO DO :

FOR SELECT  INTO  DO
FOR SELECT  INTO  DO 
   
or

FOR SELECT  INTO  DO
BEGIN
   SELECT  INTO 
   
END

Interpreting FOR SELECT as a kind of a "temporary" table, you can use it to work with multiple "temporary" tables. This may be useful if you have another family member's table with the name CASH2 (its structure is similar to that of CASH):

CREATE TABLE CASH2 (
   ID INTEGER,
   NAME VARCHAR(30),
   MONEY INTEGER,
   RDATE TIMESTAMP);
 

To sum up the total result of both members as:

you can use the procedure:

CREATE PROCEDURE RESULT3 (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
   RETURNS (
   NAME VARCHAR(30),
   SUM INTEGER)
AS
   DECLARE VARIABLE S INTEGER;
   DECLARE VARIABLE TMONEY INTEGER;
   DECLARE VARIABLE TMONEY2 INTEGER;
BEGIN
   /* Initialization */
   S=0;
   /* Selection */
   FOR SELECT NAME, SUM(MONEY) FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD GROUP    BY NAME
   INTO :NAME, :TMONEY
   DO BEGIN
		/* Selection from the second table */
		TMONEY2=0;
		SELECT SUM(MONEY) FROM CASH2 
		WHERE RDATE BETWEEN :NGOD AND :EGOD AND NAME=:NAME 
		GROUP BY NAME
		INTO :TMONEY2;
   
		/* handling returned NULL records */
		IF (TMONEY IS NULL) THEN TMONEY=0;
		IF (TMONEY2 IS NULL) THEN TMONEY2=0;
   
		/* summing up and showing the results */
		SUM=TMONEY+TMONEY2;
		S=S+SUM;
		SUSPEND;
	END
   
   /* counting records that are present in the second table and lack in the first    */
   FOR SELECT NAME, SUM(MONEY) 
   FROM CASH2 
   WHERE RDATE BETWEEN :NGOD AND :EGOD AND
   NAME NOT IN (SELECT DISTINCT NAME FROM CASH)
   GROUP BY NAME
   INTO :NAME, :SUM
   DO BEGIN
		S=S+SUM;
		SUSPEND;
   END
   /* showing the total sum */
   NAME='TOTAL';
   SUM=S;
   SUSPEND;
END

The third method is used when the task does not allow preliminary selection sorting. First you need to make a pseudo temporary table with necessary columns, fill it out in a procedure and then handle records there. To illustrate this method we will do the previous task using pseudo temporary tables.

/* we need to make a pseudo temporary table */
CREATE TABLE TEMP (
   NAME VARCHAR(30), // a field for the report
   INCOME INTEGER, // a field for the report
   EXPENSES INTEGER, // a field for the report
   TUSER VARCHAR(8), // as there may be multiple user access to    the table, 
   it is necessary to set who a record belongs to. Otherwise 
   you won't distinguish records of different users if the report will be done    in several transactions 
   
   TDATE TIMESTAMP // the date of of record listing. It is necessary    to control old undeleted records
)
   
   /* The procedure of forming of the report */
CREATE PROCEDURE RESULT4 (
   NGOD TIMESTAMP,
   EGOD TIMESTAMP)
   RETURNS (
   NAME VARCHAR(30),
   INCOME INTEGER,
   EXPENSES INTEGER)
AS
   DECLARE VARIABLE TMONEY INTEGER;
   DECLARE VARIABLE ST VARCHAR(30);
   DECLARE VARIABLE SINCOME INTEGER;
   DECLARE VARIABLE SEXPENSES INTEGER;
BEGIN
   /* Initialization of variables */
   SINCOME=0;
   SEXPENSES=0;
   
   /* Deletion of old records, which possibly remained from previous user queries    */
   DELETE FROM TEMP WHERE TUSER=USER;
   
   /* Filling out the pseudo temporary table with a record list */
   INSERT INTO TEMP 
   SELECT DISTINCT NAME, 0, 0, USER, CAST('NOW' AS TIMESTAMP) 
   FROM CASH
   WHERE RDATE BETWEEN :NGOD AND :EGOD;
   
   /* Forming of the report */
   FOR SELECT NAME, MONEY FROM CASH INTO :ST, :TMONEY 
	DO BEGIN
		IF (:TMONEY>=0) THEN
			UPDATE TEMP SET INCOME=INCOME+:TMONEY 
			WHERE NAME=:ST AND TUSER=USER;
		ELSE
   		UPDATE TEMP SET EXPENSES=EXPENSES+:TMONEY 
		  	WHERE NAME=:ST AND TUSER=USER;
	END
	/* Showing of the result */
	FOR SELECT NAME, INCOME, EXPENSES 
	FROM TEMP 
	WHERE TUSER=USER 
	ORDER BY NAME
	INTO :NAME, :INCOME, :EXPENSES
	DO BEGIN
		SINCOME=SINCOME+INCOME;
		SEXPENSES=SEXPENSES+EXPENSES;
		SUSPEND;
	END
	/* Showing of the total sum */
	NAME='TOTAL';
	INCOME=SINCOME;
	EXPENSES=SEXPENSES;
	SUSPEND;
END

The result of the procedure execution: 

As for some reason the table TEMP may have old unnecessary records, you need to clear it regularly (using the fiel d TDATE) in such a way, for example:

 /* A small hint: keep constants used in stored procedures    in separate tables. This will enable you to change their values 
   without recompilation of the procedure */
   
CREATE TABLE SETVARS ( // a table of constants
   NAME VARCHAR(10), // it's better to make index on this field
   SETVAR VARCHAR(50),
   REMARK VARCHAR(50)
   )
 

/* Clearing procedure */
CREATE PROCEDURE CLEARTEMP 
AS
   DECLARE VARIABLE T INTEGER;
BEGIN
    /* determining the variable "T" from the table of constants    */
   FOR SELECT CAST(SETVAR AS INTEGER) 
   FROM SETVARS 
   WHERE NAME='OLDTEMP' 
   INTO :T DO
	    /* deleting all old records */
	   DELETE FROM TEMP 
	   WHERE TDATE<(CAST('NOW' AS TIMESTAMP)-:T);
END

The third method is the slowest one because you go through the table CASH twice, insert and update records in a pseudo temporary table and only then show the result. And from time to time it is also necessary to clear the table Temp. The first and second methods are more quick (they will be executed almost at the speed of record selection). I know from experience that indexing the table CASH by the sorting field NAME helps to get first query results in a few seconds even from tables with about 10 million records. And applying the third method you will get the same result time from tables with about 10 thousand records. See below test results of RESULT2 and RESULT4 procedure execution (IBExpert's data)

So first it is recommended to use the first (with FOR SELECT) and the second (FOR SELECT with keeping of previous values) methods. As a last resort, you can use the third method of pseudo temporary tables.

Appendix. Illustrations to the article: test database and script (they use the Russian charset)