Automatic Logging with Functions

Imagine you set your bot loose in the database to do some heavy lifting. Sooner or later, it's gonna trip up, mess something up, or hit some weird situation. Without logging, it might just go silent, and you'll be left scratching your head, wondering what went wrong. Automatic logging helps you:

• Track when errors and warnings pop up.
• Understand what went wrong and why.
• Make your code easier to debug and run faster.

With automatic logging, you basically create a "black box" that records stuff happening in your database and helps you hunt down bugs like a pro detective.

Setting Up Automatic Logging with Functions

1. Define a table for logs

To log errors, we need somewhere to store them. We already created the error_log table in the previous lecture:

CREATE TABLE error_log (
    id SERIAL PRIMARY KEY,                 -- Unique record ID
    error_message TEXT NOT NULL,           -- Error message
    error_time TIMESTAMP DEFAULT NOW(),    -- Time when the error happened
    function_name TEXT                     -- Name of the function that caused the error
);

This table has everything you need to record errors: the error text, when it happened, and which function it came from.

2. Create a function to write logs

Next step — let's make a universal function that writes errors into the error_log table. You'll call this function whenever you want to log an error.

CREATE OR REPLACE FUNCTION log_error(p_error_message TEXT, p_function_name TEXT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO error_log (error_message, function_name)
    VALUES (p_error_message, p_function_name);
    -- Message about successful logging
    RAISE NOTICE 'Error logged: %', p_error_message;
END;
$$ LANGUAGE plpgsql;

Let's break down this code:

1. p_error_message and p_function_name — these are the function's parameters, taking the error message and the name of the function that called it.
2. INSERT INTO error_log adds a record to the table.
3. RAISE NOTICE prints a message to the console so the developer knows the logging happened.

Now we've got the first task solved: we can log errors to our table with minimal effort.

Using the log_error Function in Real Tasks

Example 1: Logging errors when dividing by zero

Let's make a function that does simple division, but logs an error if the denominator is 0.

CREATE OR REPLACE FUNCTION divide_numbers(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    result NUMERIC;
BEGIN
    IF b = 0 THEN
        -- Call the logging function
        PERFORM log_error('Division by zero attempted!', 'divide_numbers');
        -- Throw an exception
        RAISE EXCEPTION 'Division by zero is not allowed.';
    END IF;

    -- Do the division
    result := a / b;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

• If the denominator is 0, the log_error function is called, which writes the error to the table.
• After logging the error, an RAISE EXCEPTION is thrown to notify the user.

Example call:

SELECT divide_numbers(10, 0);

Result:

• Calling this function with division by zero will write an error to the error_log table.
• The user will see an error message in their console.

Example 2: Logging when inserting invalid data

Here's an example with a function that adds a new student to the students table. If the student's name is empty, we log the event and stop execution.

CREATE OR REPLACE FUNCTION add_student(p_name TEXT)
RETURNS VOID AS $$
BEGIN
    IF p_name IS NULL OR p_name = '' THEN
        PERFORM log_error('Student name must be provided!', 'add_student');
        RAISE EXCEPTION 'Student name cannot be empty.';
    END IF;

    INSERT INTO students (name) VALUES (p_name);
END;
$$ LANGUAGE plpgsql;

Example call:

SELECT add_student('');

If we try to add a student without a name, the function will create a record in error_log with the appropriate message.

Example 3: Logging warnings

You don't always need to throw an exception for the user. Sometimes it's enough to just log a warning. Let's make a function to check a student's age:

CREATE OR REPLACE FUNCTION check_age(p_age INT)
RETURNS VOID AS $$
BEGIN
    IF p_age < 18 THEN
        -- Log a warning, but don't stop execution
        PERFORM log_error('Student age is below 18.', 'check_age');
        RAISE NOTICE 'Warning: Student age is below 18.';
    END IF;

    RAISE NOTICE 'Age check passed.';
END;
$$ LANGUAGE plpgsql;

Example call:

SELECT check_age(16);

Result:

• A warning is written to the error_log table.
• A notification in the console that the student's age is less than 18.

Logging and Exception Handling

Let's combine error logging and exception handling in a more complex function. Imagine we need to recalculate grades for students in the grades table. If the process fails for one student, the error is logged, but the operation keeps going.

CREATE OR REPLACE FUNCTION recalculate_grades()
RETURNS VOID AS $$
DECLARE
    student RECORD;
BEGIN
    FOR student IN SELECT * FROM students LOOP
        BEGIN
            -- Example task: update student's grades
            UPDATE grades SET final_grade = final_grade + 1
            WHERE student_id = student.id;

            RAISE NOTICE 'Updated grades for student %', student.name;
        EXCEPTION WHEN OTHERS THEN
            -- Log the error and keep going
            PERFORM log_error('Failed to update grades for student ' || student.name, 'recalculate_grades');
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Example call:

SELECT recalculate_grades();

This approach makes your function way more robust, since errors are logged separately and don't stop the whole process for all the data.