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.

Cet exercice fait partie du cours

Transactions and Error Handling in PostgreSQL

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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;

Modifier et exécuter le code