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.

Deze oefening maakt deel uit van de cursus

Transactions and Error Handling in PostgreSQL

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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;

Code bewerken en uitvoeren