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.

Este ejercicio forma parte del curso

Transactions and Error Handling in PostgreSQL

Ver curso

Instrucciones del ejercicio

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.

Ejercicio interactivo práctico

Prueba este ejercicio y completa el código de muestra.

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;

Editar y ejecutar código