Getting stacked diagnostics
Stacked diagnostics can get the internal PostgreSQL error message and exception details. Let's revisit our patients table and try to add an A1C that is above the testing limit. This will cause a check constraint exception that we can capture. We can use the stacked diagnostics in the exception handler to enrich our error recording.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
DECLARE
two variablesexc_message
andexc_detail
as text.- Get the diagnostics stack and set
exc_message
to be theMESSAGE_TEXT
andexc_detail
to be thePG_EXCEPTION_DETAIL
. - Insert
exc_message
andexc_detail
into themsg
anddetail
field of the errors table.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
DO $$
-- Declare our text variables: exc_message and exc_detail
___
___ text;
exc_detail ___;
BEGIN
INSERT INTO patients (a1c, glucose, fasting)
values (20, 89, TRUE);
EXCEPTION
WHEN others THEN
-- Get the exception message and detail via stacked diagnostics
___ ___ ___
exc_message = ___,
exc_detail = ___;
-- Record the exception message and detail in the errors table
INSERT INTO errors (___, detail) VALUES (exc_message, ___);
END;
$$ language 'plpgsql';
-- Select all the errors recorded
SELECT * FROM errors;