You can also use the CREATE TABLE statement to create a temporary table. The CREATE TABLE syntax for temporary tables is more restrictive than for permanent tables. For this reason, and because of other characteristics, temporary tables are discussed separately. See Other Characteristics.
A temporary table is used for intermediate results or working storage. Unlike in permanent tables, data in a temporary table is destroyed at some point during the SQL session or at the end of the SQL session. The data is not saved in the database.
Temporary tables are useful to narrow down intermediate results by continuing to operate on intermediate tables. Complex data operations are often easier if split into a sequence of simpler steps, which each step operating on the table result of a previous step. A temporary table is a base table. That is, the data it contains is its own. Contrast this with a view, which is an indirect representation of data in other tables.
Zen supports two types of temporary tables:
•Local
•Global
Both types can be used within a stored procedure.
The following table summarizes characteristics of temporary tables contrasted with where the table is created or used. Characteristics can vary depending on whether the table is created or used within or outside of a stored procedure. Additional remarks are discussed as footnotes at the end of the table.
Except for permissible length of the temporary table name, the characteristics are the same for both V1 and V2 metadata.
Context of table same as database in which table is created
yes
yes
yes
yes
Two or more sessions can create table with same name2
yes
yes
no
no
For V1 metadata, see Identifier Restrictions by Identifier Type in Advanced Operations Guide for the maximum length of a table name (the length includes #, ##, underscores, and ID).
yes3
yes4
yes3
yes4
For V2 metadata, see Identifier Restrictions by Identifier Type in Advanced Operations Guide for the maximum length of a table name (the length includes #, ##, underscores, and ID).
yes3
yes4
yes3
yes4
Table in another database can be accessed by qualifying table name with other database name
no
no
yes
yes
SELECT, INSERT, UPDATE, and DELETE statements permitted on table
yes
yes
yes
yes
ALTER TABLE and DROP TABLE statements permitted on table
yes
yes
yes
yes
Can create view on table
no
no
no
no
Can create user-defined function on table
no
no
no
no
Can create trigger on table
no
no
no
no
Can grant or revoke permissions on table
no
no
no
no
FOREIGN KEY constraint allowed with CREATE TABLE statement5
no
no
no
no
SELECT INTO statement can populate table with data
yes
yes
yes
yes
SELECT INTO statement can create table6
yes
yes
yes
yes
Table created in one SQL session can be accessed by other SQL sessions
no
no
yes
yes
Table created in procedure can be accessed outside of that procedure
not applicable
no
not applicable
yes
Table created in topmost procedure can be accessed by nested procedures
not applicable
no
not applicable
no
CREATE TABLE statement in a recursive stored procedure returns table name error on recursive call
not applicable
yes7
not applicable
yes9
Table dropped when explicitly dropped
yes
yes
yes
yes
Table dropped at end of session in which table created
yes
yes8
yes
yes
Table dropped at end of procedure in which table created
not applicable
yes
not applicable
no
Table dropped at end of transaction in another session
not applicable
not applicable
yes
yes
1SP stands for stored procedure
2The database engine automatically appends the name of the stored procedure and a session-specific ID to the user-defined name to ensure a unique table name. This functionality is transparent to the user.
3The total length of the table name includes # or ##, plus an underscore, plus a session ID. The session ID can be 8, 9, or 10 bytes depending on the operating system. See Identifier Restrictions by Identifier Type in Advanced Operations Guide.
4The total length of the table name includes # or ##, plus an underscore, plus the name of the stored procedure, plus an underscore, plus a session ID. The session ID can be 8, 9, or 10 bytes depending on the operating system. See Identifier Restrictions by Identifier Type in Advanced Operations Guide.
5Constraint returns a warning but table is created.
6A table can be created and populated with data with a single SELECT INTO statement.
7The table name already exists from the first execution of the stored procedure.
8If end of session occurs before the execution of the procedure ends.
Compatibility with Previous Releases
Releases of Zen before PSQL v9 Service Pack 2 permitted the naming of permanent tables starting with # or ##. Permanent tables starting with # or ## cannot be used with PSQL v9 Service Pack 2 or later releases. Tables starting with # or ## are temporary tables and are created in the TEMPDB database.
A “table not found” error is returned if you attempt to access a permanent table starting with # or ## that was created with a version of Zen earlier than the version you are using.
The installation of Zen creates a system database named TEMPDB. TEMPDB holds all temporary tables. Never delete the TEMPDB database. If you remove it, you will be unable to create temporary tables.
TEMPDB is created in the install directory of the Zen product. See Where are the files installed? in Getting Started with Zen.
If you prefer, after installation, you may change the location of the dictionary files and data files for TEMPDB. See Database Properties in Zen User’s Guide.
Caution TEMPDB is a system database for exclusive use by the database engine. Do not use TEMPDB as a repository of your permanent tables, views, stored procedures, and so forth.
Table Names of Local Temporary Tables
The database engine automatically appends information to the names of local temporary tables to differentiate between temporary tables created across multiple sessions. The length of the appended information varies depending on the operating system.
The name of a local temporary table can be at least 10 bytes provided the number of stored procedures that create local temporary tables does not exceed 1296. The 10 bytes include the # character. The 1296 limit applies to stored procedures within the same session.
The maximum name length is 20 bytes, including the # character, the table name, and the appended information.
Transactions
A global temporary table can be explicitly dropped or is automatically dropped when the session in which the table was created ends. If a session other than the one that created the table uses the table in a transaction, the table is dropped when the transaction completes.
SELECT INTO
You can create a temporary table and populate it with data by using a single SELECT INTO statement. For example, SELECT * INTO #mytmptbl FROM Billing creates a local temporary table named #mytmptbl (provided #mytmptbl does not already exist). The temporary table contains the same data as the Billing table in the Demodata sample database.
If the SELECT INTO statement is executed a second time with the same temporary table name, an error returns because the temporary table already exists.
The SELECT INTO statement can create a temporary table from two or more tables. However, the column names must be unique in each of the tables from which the temporary table is created or an error returns.
The error can be avoided if you qualify the column names with the table names and provide an alias for each column. For example, suppose that table t1 and t2 both contain columns col1 and col2. The following statement returns an error: SELECT t1.co1, t1.col2, t2.col1, t2.col2 INTO #mytmptbl FROM t1, t2. Instead, use a statement such as this: SELECT t1.co1 c1, t1.col2 c2, t2.col1 c3, t2.col2 c4 INTO #mytmptbl FROM t1, t2.
Restrictions on SELECT INTO
•A local temporary table created within a stored procedure is inside the scope of the stored procedure. The local temporary table is destroyed after the stored procedure executes.
•The UNION and UNION ALL keywords are not permitted with a SELECT INTO statement.
•Only one temporary table can receive the results of the SELECT INTO statement. You cannot SELECT data into multiple temporary table with a single SELECT INTO statement.
Caching of Stored Procedures
Any stored procedure that references a local or a global temporary table is not cached, regardless of the cache settings. See SET CACHED_PROCEDURES and SET PROCEDURES_CACHE.
Examples of Temporary Tables
The following example creates a local temporary table named #b_temp and populates it with the data from the Billing table in the Demodata sample database.
SELECT * INTO "#b_temp" FROM Billing
============
The following example creates a global temporary table named ##tenurefac with columns ID, Dept_Name, Building_Name, Room_Number, and a primary key based on column ID.
CREATE TABLE ##tenurefac
(ID UBIGINT,
Dept_Name CHAR(20) CASE,
Building_Name CHAR(25) CASE,
Room_Number UINTEGER,
PRIMARY KEY (ID))
============
The following example alters temporary table ##tenurefac and adds the column Research_Grant_Amt.
ALTER TABLE ##tenurefac ADD Research_Grant_Amt DOUBLE
============
The following example drops temporary table ##tenurefac.
DROP TABLE ##tenurefac
============
The following example creates two temporary tables within a stored procedure, populates them with data, then assigns values to variables. The values are selected from the temporary tables.
Note SELECT INTO is permitted within a stored procedure if used to assigned values to variables.
CREATE PROCEDURE "p11"()
AS BEGIN
DECLARE :val1_int INTEGER;
DECLARE :val2_char VARCHAR(20);
CREATE TABLE #t11 (col1 INT, col2 VARCHAR(20));
CREATE TABLE #t12 (col1 INT, col2 VARCHAR(20));
INSERT INTO #t11 VALUES (1,'t1 col2 text');
INSERT INTO #t12 VALUES (2,'t2 col2 text');
SELECT col1 INTO :val1_int FROM #t11 WHERE col1 = 1;
SELECT col2 INTO :val2_char FROM #t12 WHERE col1 = 2;
PRINT :val1_int;
PRINT :val2_char;
COMMIT;
END;
CALL P11()
============
The following example creates global temporary table ##enroll_student_global_temp_tbl and then creates stored procedure Enrollstudent. When called, the procedure inserts a record into ##enroll_student_global_temp_tbl, given the Student ID, Class ID, and a grade point average (GPA). A SELECT selects all records in the temporary table and displays the result. The length of the name for the global temporary table is permissible only for V2 metadata.
CREATE PROCEDURE Enrollstudent(in :Stud_id integer, in :Class_Id integer, IN :GPA REAL);
BEGIN
INSERT INTO ##enroll_student_global_temp_tbl VALUES(:Stud_id, :Class_id, :GPA);
END;
CALL Enrollstudent(1023456781, 146, 3.2)
SELECT * FROM ##enroll_student_global_temp_tbl
============
The following example creates two temporary tables within a stored procedure, populates them with data, then assigns values to variables. The values are selected from the temporary tables.
CREATE PROCEDURE "p11"()
AS BEGIN
DECLARE :val1_int INTEGER;
DECLARE :val2_char VARCHAR(20);
CREATE TABLE #t11 (col1 INT, col2 VARCHAR(20));
CREATE TABLE #t12 (col1 INT, col2 VARCHAR(20));
INSERT INTO #t11 VALUES (1,'t1 col2 text');
INSERT INTO #t12 VALUES (2,'t2 col2 text');
SELECT col1 INTO :val1_int FROM #t11 WHERE col1 = 1;
SELECT col2 INTO :val2_char FROM #t12 WHERE col1 = 2;