Capturing a context stack

Getting the stack context, which is like a stack trace in other languages, is a powerful way to debug complex and nested functions.

In the code below, we want to capture the stack context and record it in the exception handlers of both nested blocks. Then, we want to review its output in the errors table to help debug what's causing the exception in this function.

Este exercício faz parte do curso

Transactions and Error Handling in PostgreSQL

Ver curso

Instruções do exercício

Declare a text variable, exc_context, to hold the stack context.
Store the PG_EXCEPTION_CONTEXT in our exc_context variable in the first handler.
Store the PG_EXCEPTION_DETAIL in our exc_detail variable in the second handler.
Record both the error message and the stack context in both blocks.

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

DO $$
DECLARE
   exc_message text;
   exc_details text;
   -- Declare a variable, exc_context to hold the exception context
   ___ ___;
BEGIN
    BEGIN
    	INSERT INTO patients (a1c, glucose, fasting) values (5.6, 93, TRUE),
        	(6.3, 111, TRUE),(4.7, 65, TRUE);
    EXCEPTION
        WHEN others THEN
        -- Store the exception context in exc_context
        GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
                                ___ = ___;
        -- Record both the msg and the context
        INSERT INTO errors (msg, context) 
           VALUES (___, ___);
    END;
	BEGIN
    	UPDATE patients set fasting = 'true' where id=1;
    EXCEPTION
        WHEN others THEN
        -- Store the exception detail in exc_details
        GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
                                ___ = ___;
        INSERT INTO errors (___, ___) 
           VALUES (exc_message, exc_context);
    END;
END$$;
SELECT * FROM errors;

Editar e executar o código