Function Supporting Autonomous Transaction

An autonomous transaction can be defined in a function. The identifier of an autonomous transaction is PRAGMA AUTONOMOUS_TRANSACTION. The syntax of an autonomous transaction is the same as that of creating a function. For details, see CREATE FUNCTION. The following is an example:

gaussdb=# CREATE TABLE t4(a INT, b INT, c TEXT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

gaussdb=# CREATE OR REPLACE FUNCTION autonomous_32(a INT ,b INT ,c TEXT) RETURN INT AS 
DECLARE 
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO t4 VALUES(a, b, c);
 RETURN 1;
END;
/
CREATE FUNCTION
gaussdb=# CREATE OR REPLACE FUNCTION autonomous_33(num1 INT) RETURN INT AS 
DECLARE 
 num3 INT := 220;
 tmp INT;
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 num3 := num3/num1;
 RETURN num3;
EXCEPTION
 WHEN DIVISION_BY_ZERO THEN
  SELECT autonomous_32(num3, num1, SQLERRM) INTO tmp;
  ROLLBACK;
  RETURN 0;
END;
/
CREATE FUNCTION
gaussdb=# SELECT autonomous_33(0);
 autonomous_33 
---------------
             0
(1 row)

gaussdb=# SELECT * FROM t4;
  a  | b |        c         
-----+---+------------------
 220 | 0 | division by zero
(1 row)

gaussdb=# DROP TABLE t4;
DROP TABLE

In the preceding example, a function containing an autonomous transaction is finally executed in a transaction block to be rolled back, which directly illustrates a characteristic of the autonomous transaction, that is, rollback of the primary transaction does not affect content that has been committed by the autonomous transaction.