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 exercise is part of the course

Transactions and Error Handling in PostgreSQL

View Course

Exercise instructions

In the first block, insert the following records into patients (a1c=5.6, glucose=93, and fasting=TRUE), (6.3, 111, TRUE) (4.7, 65, TRUE) with an EXCEPTION handler that inserts 'failed to insert' as the msg 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 the errors table with a msg 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;

Edit and Run Code