Multiple exception blocks
Since ROLLBACK TO
and SAVEPOINT
can not be used in functions with exception handlers, we have a way to emulate the same behavior though using nested blocks. These nested blocks are used to group and order the statements in the order that they depend on each other. Here you are going to insert a block of records with an exception handler which emulates a SAVEPOINT
, then update a record with an exception handler. That update statement will error, and the exception handler will automatically rollback just that block.
This is a part of the course
“Transactions and Error Handling in PostgreSQL”
Exercise instructions
- In the first block, insert the following records into
patients
(a1c
=5.6
,glucose
=93
, andfasting
=TRUE
),(6.3, 111, TRUE)
(4.7, 65, TRUE)
with anEXCEPTION
handler that inserts'failed to insert'
as themsg
into errors. - Open a second nested block and update the patient with the ID of 1 to have a
fasting
value of'true'
with exception handling that records to theerrors
table with amsg
of'Inserted string into boolean.'
. END
both blocks.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Make a DO function
DO $$
-- Open a transaction block
BEGIN
-- Open a nested block
BEGIN
INSERT INTO ___ (___, ___, ___)
VALUES (5.6, 93, TRUE), ___, ___;
-- Catch all exception types
EXCEPTION WHEN others THEN
INSERT INTO ___ (msg) VALUES ('failed to insert');
-- End nested block
END;
-- Begin the second nested block
BEGIN
UPDATE patients SET ___ = '___' WHERE id=1;
-- Catch all exception types
EXCEPTION WHEN others THEN
INSERT INTO errors (msg) VALUES ('Inserted ___ into ___.');
-- End the second nested block
END;
-- END the outer block
END;
$$ language 'plpgsql';
SELECT * FROM errors;