Return a result set

Typically, an NZPLSQL procedure returns a unique return value, but it can also return a result set in the form of a specified table.

To create a stored procedure that returns a result set:
  • Define the stored procedure with a return value of “RETURNS REFTABLE (<table-name>)” to indicate that it returns a result set that looks like the specified table.
  • Inside the body of the procedure, use the variable REFTABLENAME to refer to the results table.

The table specified in the RETURNS value must exist at the time that the stored procedure is created, although the table can be empty. The table continues to exist after the stored procedure completes. You cannot drop the reference table while the stored procedure is defined. (That is, you must drop the stored procedure or modify it to return a different reference table before you can drop the table.)

For example, the following stored procedure, returntwo, returns a result set by using the reference table feature. The reference table that it uses, tbl, was previously defined by using the following command:
CREATE TABLE tbl (i INT4, i2 bigint);
A description of tbl follows:
DEV.SCH1(ADMIN)=> \d tbl
                  Table "TBL"
 Attribute |  Type   | Modifier | Default Value 
-----------+---------+----------+---------------
 I         | INTEGER |          | 
 I2        | BIGINT  |          | 
Distributed on hash: "I"
After you confirm that the reference table exists, you can use the following command to define the stored procedure returntwo:
DEV.SCH1(ADMIN)=> CREATE OR REPLACE PROCEDURE returntwo(timestamp) RETURNS 
REFTABLE(tbl) LANGUAGE NZPLSQL AS 
BEGIN_PROC
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)';
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)';
    RETURN REFTABLE;
  END;
  END_PROC;
A sample call to the returntwo stored procedure follows:
DEV.SCH1(ADMIN)=> EXECUTE PROCEDURE returntwo(now());
 I | I2 
---+----
 1 |  1
 2 |  2
(2 rows)
Restriction: You cannot specify a WHERE clause in a query that calls a stored procedure that returns a result set.
When you call or invoke the stored procedure by using a SQL command such as SELECT procedure(), CALL procedure(), EXECUTE procedure(), and so on, the database does the following:
  • Generates a table name TEMPFUNC<oid> where oid is the object ID of the procedure that was invoked
  • Checks if the table name exists; if it does, it issues a DROP TABLE <temp-table-name> command
  • Issues a CREATE TEMPORARY TABLE <temp-table-name> as select * from <table-name> LIMIT 0 to create the table for the results set with no initial contents
  • Returns the results of SELECT * from <temp-table-name> where proc(args) is NULL (This situation is the only situation in which a stored procedure is allowed to be invoked with a FROM clause and where the return value is used as part of a query.)

To use this in a procedure, you must insert your results in <temp-table-name> by using the REFTABLENAME variable to obtain the name. This SQL command must be invoked dynamically to use the variable.

Additionally, you must return NULL in your procedure by one of the following means:
  • RETURN REFTABLE;
  • RETURN NULL;
  • RETURN;
  • Not specifying a RETURN clause.

If you do not return NULL, the procedure returns an error. The recommended method to return NULL is RETURN REFTABLE.

One REFTABLE procedure can call another, but you encounter unusual results if a REFTABLE procedure calls itself (either directly or recursively) because of the temporary table logic; therefore, avoid designing a REFTABLE procedure which calls itself.

Cross-database access for a REFTABLE procedure should work without problem as the temporary table will be created in the local database; it will retrieve the shape of the REFTABLE definition in the other database.

The SQL that is executed (for example, CREATE TEMPORARY TABLE, DROP TABLE) uses the owner ID of the procedure as the effective user ID if EXECUTE AS OWNER is set; otherwise, if EXECUTE AS CALLER is set, the SQL uses the user ID of the account which calls or invokes the procedure.