REFTABLE and ROLLBACK commands

If a stored procedure returns a result set (REFTABLE), do not issue a ROLLBACK command inside the procedure body unless you first issue a COMMIT to create the temporary table for REFTABLE. Issuing a ROLLBACK without the prior COMMIT command causes your REFTABLE procedure to fail when executed.

As a best practice, if your procedure body includes a ROLLBACK command, or if you call another procedure that could issue a ROLLBACK, specify the COMMIT command as the first statement in the REFTABLE procedure body. An example follows:

DEV.SCH1(ADMIN)=> CREATE OR REPLACE PROCEDURE returntwo(timestamp) RETURNS 
REFTABLE(tbl) LANGUAGE NZPLSQL AS 
BEGIN_PROC
  BEGIN
    COMMIT;
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)';
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)';
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (3,3)';
    COMMIT;
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (4,4)';
    ROLLBACK 
    RETURN REFTABLE;
  END;
END_PROC;

DEV.SCH1(ADMIN)=> EXECUTE returntwo(now()); 
 I | I2
---+----
 2 |  2
 1 |  1
 3 |  3
(3 rows)

As shown in the example, your procedure can call the COMMIT command as needed, but the first COMMIT ensures that the temporary table exists for processing within the body.

The following message is an example of the error that occurs when the ROLLBACK ran without a prior COMMIT:

DEV.SCH1(ADMIN)=> EXECUTE returntwo(now());
NOTICE:  Error occurred while executing PL/pgSQL function RETURNTWO
NOTICE:  line 1 at SQL statement
ERROR:  Cannot use ROLLBACK from within a stored procedure which uses 
a REFTABLE without a COMMIT first.