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.

Diese Übung ist Teil des Kurses

Transactions and Error Handling in PostgreSQL

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

-- 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;

Code bearbeiten und ausführen