16.4. PL/pgSQL Grammar Extension for Autonomous Transactions #
Block construction in PL/pgSQL is extended by the optional autonomous
keyword. It is possible to treat the whole function body as an autonomous transaction:
CREATE FUNCTION foo(x integer) RETURNS integer AS $$ BEGIN AUTONOMOUS RETURN x; END; $$ LANGUAGE plpgsql;
or create a separate BEGIN
/END
block:
CREATE OR REPLACE FUNCTION myaudit() RETURNS boolean AS $$ BEGIN AUTONOMOUS BEGIN AUTONOMOUS INSERT INTO audit_schedule VALUES ('new audit',now()); END; ... -- do audit itself RETURN true; END; $$ LANGUAGE plpgsql;
Note
You cannot redefine isolation level for autonomous transactions within PL/pgSQL blocks.
If an exception is raised inside a BEGIN AUTONOMOUS
block, this autonomous transaction is aborted, and the standard exception handling procedure is started, unwinding the stack and executing exception handlers until the exception is caught. Thus, exception handling for autonomous transactions in PL/pgSQL is done in the same way as for Postgres Pro subtransactions.
If an error is caught by an EXCEPTION
clause, local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back.