डिबगिंग फंक्शन के साथ सबको मिलाकर

PostgreSQL में Transactions और Error Handling

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 में Transactions और Error Handling