Handling exceptions

In the slides, we discussed providing proper context for resolution. One area that is often overlooked when recording messages is the deeper reasoning for them. Oftentimes errors are generic like "Bad value" or "Invalid date." However, we can use details and context to enrich those messages.

Here we are going to work with A1C which is the percentage of red blood cells that have sugar attached to the hemoglobin. Typically fasting ranges are below 5.7% for non-affected patients, 5.7% to 6.4% for prediabetes, and over 6.5% is typically an indicator of unmanaged diabetes.

This exercise is part of the course

Transactions and Error Handling in PostgreSQL

View Course

Exercise instructions

Complete the transaction block by filling out the DO and BEGIN statements where applicable.
Add an exception that inserts 'a1c is typically less than 14' into the context column of the errors table.
End the exception by specifying the procedural language used.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Add a DO function
___ $$ 
-- BEGIN a transaction block
___ 
    INSERT INTO patients (a1c, glucose, fasting) 
    values (20, 89, TRUE);

-- Add an EXCEPTION                   
____ 
-- Catch all exception types
WHEN others THEN
    INSERT INTO errors (msg, detail, ___) VALUES 
  (
    'failed to insert', 
    'This a1c value is higher than clinically accepted norms.', 
    '___'
  );
END;
-- Make sure to specify the language
____ language 'plpgsql';

-- Select all the errors recorded
SELECT * FROM errors;

Edit and Run Code