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
Exercise instructions
- Complete the transaction block by filling out the
DO
andBEGIN
statements where applicable. - Add an exception that inserts
'a1c is typically less than 14'
into thecontext
column of theerrors
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;