How to insert an error into another table from a function in postgres?

Question

I am looking to understand how to properly handle errors and move them. I would like to take an error and dump that error into another table which I plan to scan later. How would i go about doing that in the example below?

-----my main table
CREATE TABLE stage(test_date VARCHAR);

-----error table
CREATE TABLE errors (
error VARCHAR,
the_date timestamp
);

-----function that will handle insert and log error into errors table
CREATE OR REPLACE PROCEDURE example4 () AS $$
BEGIN
  INSERT INTO prd (test_date) VALUES ('hello');
  EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Insert failed with error code %', SQLSTATE;
        ---INSERT INTO errors table???
        ---Im imagining something like INSERT INTO errors (error, the_date) ...
END;
$$
LANGUAGE plpgsql;

LoF10 · Accepted Answer · 2020-03-24 22:32:20Z

Found and answer using a function from:

PGSQL Trigger Function Write Exception to Log Table

CREATE TABLE errors (id SERIAL, sql_state TEXT, message TEXT, detail TEXT, hint TEXT, context TEXT);

CREATE OR REPLACE FUNCTION example4()
    RETURNS VOID AS
$BODY$
DECLARE
    _sql_state TEXT;
    _message TEXT;
    _detail TEXT;
    _hint TEXT;
    _context TEXT;
BEGIN
    INSERT INTO prd (test_date) VALUES ('hello');
EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            _sql_state := RETURNED_SQLSTATE,
            _message := MESSAGE_TEXT,
            _detail := PG_EXCEPTION_DETAIL,
            _hint := PG_EXCEPTION_HINT,
            _context := PG_EXCEPTION_CONTEXT;

        INSERT INTO errors (sql_state, message, detail, hint, context)
        VALUES (_sql_state, _message, _detail, _hint, _context);
END
$BODY$
    LANGUAGE plpgsql;

Collectives™ on Stack Overflow

How to insert an error into another table from a function in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest