The ROLLBACK command undoes all database updates made during the current transaction and ends the current transaction.
You can use the ROLLBACK command in anonymous blocks, stored procedures, or functions. In an SPL program, it can appear in the executable section and the exception section.
In this example, the exception section contains a ROLLBACK command. Even though the first two INSERT commands execute successfully, the third causes an exception that results in the rollback of all the INSERT commands in the anonymous block.
This example uses both COMMIT and ROLLBACK. First, the following stored procedure is created. It inserts a new employee.
This procedure has no exception section. Any errors are propagated up to the calling program.
Then the following anonymous block runs. The COMMIT command is used after all calls to the emp_insert procedure and the ROLLBACK command in the exception section.
The following SELECT command shows that employees Farrell and Tyler were successfully added:
Next, execute the following anonymous block:
A SELECT command run against the table produces the following:
The ROLLBACK command in the exception section successfully undoes the insert of employee Harrison. Employees Farrell and Tyler are still in the table as their inserts were made permanent by the COMMIT command in the first anonymous block.
Executing a COMMIT or ROLLBACK in a plpgsql procedure throws an error if an Oracle-style SPL procedure is on the runtime stack.