16.2. Usage#
16.2. Usage #
To enable autonomous transactions for a PL/pgSQL function, add the pragma:
PRAGMA AUTONOMOUS_TRANSACTION;
This will start an autonomous session when entering the function block and end it when exiting. Any SQL commands within the block will then execute in the autonomous session.
For example:
CREATE FUNCTION process_orders() RETURNS void AS $$
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR order IN SELECT * FROM orders LOOP
-- start transaction
START TRANSACTION;
-- insert order
INSERT INTO processed_orders VALUES (...);
-- check for errors
IF (<error condition>) THEN
ROLLBACK;
CONTINUE;
END IF;
-- commit on success
COMMIT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
This will process each order in a separate transaction, avoiding any rollback of previously committed orders if one fails.
The autonomous session has its own database connection and transaction state. Regular session commands like START TRANSACTION, COMMIT, and ROLLBACK manipulate the autonomous transaction.
Cursors and prepared statements are currently not supported in autonomous transactions.