Error Logging: Logging Levels and Message Formatting

Right now, we're kinda like secret agents — our functions and procedures are on missions: processing data, doing calculations, or just working some magic inside the database. But how do you know if something goes sideways? How do you figure out at what stage your "data massage bathtub" broke down? That's where logging and error handling come to the rescue.

Remember, we already started checking out how you can "talk" and keep logs in PostgreSQL and PL/pgSQL:

• RAISE NOTICE: chill, friendly tone — "Hey, everything's cool here, but you might wanna take a look at this."
• RAISE WARNING: a bit louder — "Whoa, something's weird here, maybe check it out."
• RAISE EXCEPTION: panic siren — "STOP! The algorithm's in trouble! We stopped execution so everything doesn't go down the drain."

Each of these levels has its own purpose, and it's important to pick the right one for the job.

Here's what these messages look like in code:

DO $$
BEGIN
    -- NOTICE level (all good, just a heads up)
    RAISE NOTICE 'Just a heads up: data processing started';

    -- WARNING level (something fishy)
    RAISE WARNING 'Warning: data format in the column might be off';

    -- EXCEPTION level (critical error)
    RAISE EXCEPTION 'Error: input value is not allowed!';
END $$;

When to use:

• RAISE NOTICE — for debugging and chill info output.
• RAISE WARNING — to warn about potentially bad data.
• RAISE EXCEPTION — when critical errors happen and the function needs to stop running.

Error Handling with RAISE EXCEPTION

RAISE EXCEPTION is your emergency brake. If something goes wrong, you can stop the function and let everyone know about the error.

Just a reminder, the basic usage looks like this:

RAISE EXCEPTION 'Your error message';

But to make your messages more helpful, you can use variables:

DECLARE
    input_value INTEGER;
BEGIN
    input_value := NULL;

    IF input_value IS NULL THEN
        RAISE EXCEPTION 'Error: input value is NULL. Expected an INTEGER value';
    END IF;
END;

Message Formatting

You can plug variables right into your message text:

DECLARE
    var1 TEXT := 'Data';
    var2 INTEGER := 42;
BEGIN
    RAISE EXCEPTION 'Error processing % with ID %', var1, var2;
END;

Output: Error processing Data with ID 42.

Example: Data Validation

Imagine you have a procedure that takes a person's age. If the age is negative, it makes sense to throw an error:

CREATE OR REPLACE FUNCTION validate_age(age INTEGER)
RETURNS VOID AS $$
BEGIN
    IF age < 0 THEN
        RAISE EXCEPTION 'Age can''t be negative: %', age;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Calling the function
SELECT validate_age(-5);  -- Will throw an error

Informing with RAISE NOTICE

If RAISE EXCEPTION is a siren, then RAISE NOTICE is a friendly pat on the back. With this level, you can add comments to help understand what's happening inside your function.

When to use RAISE NOTICE:

• Outputting debug info (like the current state of variables).
• Letting folks know when a step starts or what the result of a calculation is.

Example: informational messages

CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, discount_rate NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    final_price NUMERIC;
BEGIN
    RAISE NOTICE 'Price before discount: %', price;
    RAISE NOTICE 'Discount rate: %', discount_rate;

    final_price := price - (price * discount_rate);

    RAISE NOTICE 'Final price: %', final_price;

    RETURN final_price;
END;
$$ LANGUAGE plpgsql;

-- Calling the function
SELECT calculate_discount(100, 0.2);
-- Outputs:
-- NOTICE: Price before discount: 100
-- NOTICE: Discount rate: 0.2
-- NOTICE: Final price: 80

Practical Use: Planning and Logging

Let's say you have a complex data processing procedure, and you wanna know what step it's on right now:

CREATE OR REPLACE FUNCTION process_data_step_by_step()
RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Step 1: Preparing data';
    -- Your logic for the first step

    RAISE NOTICE 'Step 2: Validating data';
    -- Your logic for the second step

    RAISE NOTICE 'Step 3: Saving data';
    -- Your logic for the third step
END;
$$ LANGUAGE plpgsql;

-- Calling the function
SELECT process_data_step_by_step();
-- The logs will show step-by-step execution

Here's another example. Let's imagine a store that only gives discounts for orders above a certain amount:

CREATE OR REPLACE FUNCTION apply_discount(order_amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF order_amount < 50 THEN
        RAISE EXCEPTION 'Error: order amount must be at least 50, current amount: %', order_amount;
    END IF;

    RETURN order_amount * 0.9;  -- Apply a 10% discount
END;
$$ LANGUAGE plpgsql;

-- Calling the function
SELECT apply_discount(30);  -- Error: order amount must be at least 50

Common Mistakes

Mistake 1: logging messages without parameters.

Looks unhelpful, especially in big procedures:

RAISE NOTICE 'An error happened';  -- Why? Where? How?

Tip: always add context:

RAISE NOTICE 'Error in function process_data(): input value: %', input_value;

Mistake 2: using RAISE EXCEPTION where RAISE WARNING would be enough.

If you go overboard with exceptions, your code will stop for every little thing, making data processing a pain.

Advice: use logging levels wisely. For debugging, go with NOTICE, and for critical stuff — EXCEPTION.

Mistake 3: not logging at all.

That's like trying to find your keys in a dark room. Without logs, debugging complex processes is almost impossible.

Advice: add RAISE NOTICE at key steps in your function, especially if it's big and complicated.