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.