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. 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;
/
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;
		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)