Mixing it all together with debugging functions

Transactions and Error Handling in PostgreSQL

Jason Myers

Principal Engineer

Named function overview

CREATE OR REPLACE FUNCTION function_name(
    parameter1 TEXT,
    parameter2 INTEGER
)
RETURNS BOOLEAN AS $$
    DECLARE
    BEGIN
        STATEMENTS
    END;
$$ LANGUAGE plpgsql;

A function for debugging

CREATE OR REPLACE FUNCTION debug_statement(
    sql_stmt TEXT
)
RETURNS BOOLEAN AS $$

    DECLARE
        v_state   TEXT;
        v_msg     TEXT;
        v_detail  TEXT;
        v_context TEXT;

    BEGIN
        BEGIN
            EXECUTE sql_stmt;

The rest of a function for debugging

        EXCEPTION WHEN others THEN
            GET STACKED DIAGNOSTICS
                v_state   = RETURNED_SQLSTATE,
                v_msg     = MESSAGE_TEXT,
                v_detail  = PG_EXCEPTION_DETAIL,
                v_context = PG_EXCEPTION_CONTEXT;
            INSERT into errors (msg, state, detail, context) 
                values (v_msg, v_state, v_detail, v_context);
            RETURN True;

        END;
        RETURN False;
    END;
$$ LANGUAGE plpgsql;

Using the function as a statement

SELECT debug_statement(
  'UPDATE inventory SET cost = 35.0 WHERE name = ''Macaron'' '
);

-[ RECORD 1 ]---+--
debug_statement | t

Reviewing the functions recording of the exception

SELECT * FROM errors;

-[ RECORD 1 ]----------------------------------------------------------------------
error_id | 20
state    | 23514
msg      | new row for relation "inventory" violates check constraint "cost_check"
detail   | Failing row contains (7, 35, Macaron).
context  | SQL statement "UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron' "
         | PL/pgSQL function debug_statement(text) line 9 at EXECUTE

Using the function with in a function

DO $$
DECLARE
    stmt VARCHAR(100) := 'UPDATE inventory SET cost = 35.0 WHERE name = ''Macaron'' ';
BEGIN
     EXECUTE stmt;
EXCEPTION WHEN OTHERS THEN
    PERFORM debug_statement(stmt);
END; $$ language 'plpgsql';

Error recording from the DO function

SELECT * FROM errors;

-[ RECORD 1 ]----------------------------------------------------------------------
error_id | 21
state    | 23514
msg      | new row for relation "inventory" violates check constraint "cost_check"
detail   | Failing row contains (7, 35, Macaron).
context  | SQL statement "UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron' "+
         | PL/pgSQL function debug_statement(text) line 9 at EXECUTE               +
         | SQL statement "SELECT debug_statement(stmt)"                            +
         | PL/pgSQL function inline_code_block line 7 at PERFORM

Let's practice!

Transactions and Error Handling in PostgreSQL