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

View Course

Exercise instructions

DECLARE two variables exc_message and exc_detail as text.
Get the diagnostics stack and set exc_message to be the MESSAGE_TEXT and exc_detail to be the PG_EXCEPTION_DETAIL.
Insert exc_message and exc_detail into the msg and detail 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;

Edit and Run Code