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.