Kombinera allt med felsökningsfunktioner

Transaktioner och felhantering i PostgreSQL

Jason Myers

Principal Engineer

Översikt av namngivna funktioner

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

En funktion för felsökning

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;

Resten av felsökningsfunktionen

        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;

Använda funktionen som ett uttryck

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

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

Granska funktionens loggning av undantaget

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

Använda funktionen inuti en annan funktion

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';

Felloggning från DO-funktionen

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

Nu kör vi en övning!

Transaktioner och felhantering i PostgreSQL