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.

Diese Übung ist Teil des Kurses

Transactions and Error Handling in PostgreSQL

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

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 bearbeiten und ausführen