1. Learn
    2. /
  2. Courses
    3. /
  3. Transactions and Error Handling in PostgreSQL

Exercise

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.

Instructions

100 XP
  • 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.