how to use COMMIT and ROLLBACK in a PostgreSQL function

Question

I am using three insert statements, and if there is an error in the third statement, I want to rollback the first and the second one. If there is no way to do this, please tell me a different approach to handle this in PostgresqQL.

If I use COMMIT or ROLLBACK, I get an error.

CREATE OR REPLACE FUNCTION TEST1 ()
   RETURNS VOID
   LANGUAGE 'plpgsql'
   AS $$
BEGIN 

    INSERT INTO table1 VALUES (1);

    INSERT INTO table1 VALUES (2);

    INSERT INTO table1 VALUES ('A');
    COMMIT;
EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
END;$$;

The above code is not working; COMMIT and ROLLBACK are not supported by PostgreSQL functions.

Answer 1

You cannot use transaction statements like SAVEPOINT, COMMIT or ROLLBACK in a function. The documentation says:

In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK.

Ex negativo, since functions are not procedures that are invoked with CALL, you cannot do that in functions.

The BEGIN that starts a block in PL/pgSQL is different from the SQL statement BEGIN that starts a transaction.

Just remove the COMMIT from your function, and you have the solution: since the whole function is always run inside a single transaction, any error in the third statement will lead to a ROLLBACK that also undoes the first two statements.