将调试函数融会贯通

PostgreSQL 中的事务与错误处理

Jason Myers

Principal Engineer

命名函数概览

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

用于调试的函数

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;

调试函数的其余部分

        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;

将函数作为语句使用

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

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

查看函数记录的异常

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

在函数中使用该函数

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

从 DO 函数记录的错误

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

让我们练习！

PostgreSQL 中的事务与错误处理