tab_error Table

CREATE TABLE IF NOT EXISTS tab_error(
    cod_error       VARCHAR     NOT NULL,
    name_error      VARCHAR     NOT NULL,
    PRIMARY KEY (cod_error)
);

-- Success
INSERT INTO tab_error VALUES ('00000', 'Successful Completion: Operación ejecutada sin errores.');

-- Constraint violations (most common in payroll system)
INSERT INTO tab_error VALUES ('23502', 'Not Null Violation: Columna no permite nulos.');
INSERT INTO tab_error VALUES ('23503', 'Foreign Key Violation: Violación de clave foránea.');
INSERT INTO tab_error VALUES ('23505', 'Unique Violation: Violación de restricción única.');
INSERT INTO tab_error VALUES ('23514', 'Check Violation: Violación de restricción CHECK.');

-- Data exceptions
INSERT INTO tab_error VALUES ('22012', 'Division By Zero: División por cero.');
INSERT INTO tab_error VALUES ('22003', 'Numeric Value Out Of Range: Valor numérico fuera de rango.');
INSERT INTO tab_error VALUES ('22001', 'String Data Right Truncation: Cadena truncada a la derecha.');

-- Syntax and access errors
INSERT INTO tab_error VALUES ('42601', 'Syntax Error: Error de sintaxis.');
INSERT INTO tab_error VALUES ('42501', 'Insufficient Privilege: Privilegios insuficientes.');

-- PL/pgSQL errors
INSERT INTO tab_error VALUES ('P0001', 'Raise Exception: Se ejecutó RAISE EXCEPTION.');
INSERT INTO tab_error VALUES ('P0002', 'No Data Found: No se encontraron datos en PL/pgSQL.');

SELECT name_error
FROM tab_error
WHERE cod_error = '23505';

SELECT cod_error, name_error
FROM tab_error
WHERE name_error ILIKE '%violación%'
ORDER BY cod_error;

SELECT cod_error, name_error
FROM tab_error
WHERE cod_error LIKE '23%'
ORDER BY cod_error;

SELECT cod_error, name_error
FROM tab_error
WHERE cod_error LIKE 'P0%'
ORDER BY cod_error;

CREATE OR REPLACE FUNCTION get_error_description(error_code VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    error_desc VARCHAR;
BEGIN
    SELECT name_error INTO error_desc
    FROM tab_error
    WHERE cod_error = error_code;
    
    RETURN COALESCE(error_desc, 'Error desconocido: ' || error_code);
END;
$$ LANGUAGE plpgsql;

# Python example
import psycopg2

def insert_employee(conn, employee_data):
    try:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO tab_emplea VALUES (%s, %s, ...)",
            employee_data
        )
        conn.commit()
    except psycopg2.Error as e:
        sqlstate = e.pgcode
        
        # Look up user-friendly error message
        cursor.execute(
            "SELECT name_error FROM tab_error WHERE cod_error = %s",
            (sqlstate,)
        )
        error_desc = cursor.fetchone()
        
        if error_desc:
            print(f"Error: {error_desc[0]}")
        else:
            print(f"Error code: {sqlstate}")
        
        conn.rollback()

-- Create error log table
CREATE TABLE error_log (
    log_id SERIAL PRIMARY KEY,
    error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    error_code VARCHAR,
    error_message TEXT,
    user_name VARCHAR,
    query_text TEXT
);

-- Log error with description
INSERT INTO error_log (error_code, error_message, user_name)
SELECT 
    '23505',
    name_error,
    current_user
FROM tab_error
WHERE cod_error = '23505';