Logging with RAISE NOTICE

Logging is the process of recording important events and info about how your app or database is working. In PL/pgSQL, it's especially handy when you're building complex functions that call other functions, work with triggers, or do a bunch of steps. Here are a few key reasons why logging is a must:

1. Debugging code: logging helps you figure out what's actually happening at each stage of your function's execution.
2. Problem diagnosis: if your function isn't working as expected, logs help you pinpoint exactly where things went wrong.
3. Execution analysis: knowing which steps ran (and in what order) lets you optimize performance or spot places to improve.
4. Easier maintenance: imagine opening your function a year from now and having no clue how it works (trust me, it happens). Logs save you in those moments.

RAISE NOTICE — the core of logging

If you're a programmer and have ever used print() or console.log() for debugging, then RAISE NOTICE is like their older sibling in the PostgreSQL world. It's a command that prints info messages while your function runs. These messages show up in the console, terminal, or whatever interface you're running your query in.

Syntax for RAISE NOTICE

RAISE NOTICE 'Execution message';

But that's just the start. You can include variables in your messages to make them more informative:

RAISE NOTICE 'Current value of variable: %', my_variable;

Here, % acts as a placeholder, and my_variable is the variable whose value you want to print.

If you want to print several variables, you can do it like this:

RAISE NOTICE 'Values: % and %', var1, var2;

Examples of using RAISE NOTICE

1. Printing a variable's value. Let's declare a variable in a function and print its value using RAISE NOTICE.

CREATE OR REPLACE FUNCTION debug_variable_example()
RETURNS VOID AS $$
DECLARE
    my_variable INTEGER := 42;
BEGIN
    RAISE NOTICE 'Value of variable my_variable: %', my_variable;
END;
$$ LANGUAGE plpgsql;

When you call this function:

SELECT debug_variable_example();

In the results, you'll see something like:

NOTICE:  Value of variable my_variable: 42

2. Logging execution steps. Imagine you have a function that needs to do several steps. You can add RAISE NOTICE after each action to make sure everything's going according to plan.

CREATE OR REPLACE FUNCTION process_data()
RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Step 1: process started';

    -- Some SQL code runs here
    PERFORM pg_sleep(1); -- Simulating an operation

    RAISE NOTICE 'Step 2: process continues';

    -- More SQL
    PERFORM pg_sleep(1); -- Simulating an operation

    RAISE NOTICE 'Step 3: process finished';
END;
$$ LANGUAGE plpgsql;

Calling the function:

SELECT process_data();

You'll get:

NOTICE:  Step 1: process started
NOTICE:  Step 2: process continues
NOTICE:  Step 3: process finished

Now you always know which stage is running.

Practical use

Let's look at an example of logging calculations. We'll make a function that calculates the sum of numbers from 1 to N and logs the process:

CREATE OR REPLACE FUNCTION sum_with_logging(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    total INTEGER := 0;
    i INTEGER;
BEGIN
    RAISE NOTICE 'Starting calculations for n = %', n;

    FOR i IN 1..n LOOP
        total := total + i;
        RAISE NOTICE 'Sum at step %: %', i, total;
    END LOOP;

    RAISE NOTICE 'Calculation result: %', total;

    RETURN total;
END;
$$ LANGUAGE plpgsql;

Calling this function with the parameter 5:

SELECT sum_with_logging(5);

You'll get:

NOTICE:  Starting calculations for n = 5
NOTICE:  Sum at step 1: 1
NOTICE:  Sum at step 2: 3
NOTICE:  Sum at step 3: 6
NOTICE:  Sum at step 4: 10
NOTICE:  Sum at step 5: 15
NOTICE:  Calculation result: 15

Handy logging tips

1. Get rid of unnecessary logs: when your function is ready and debugged, remove extra RAISE NOTICE messages so you don't clutter up the terminal or user interface.
2. Use meaningful messages: don't just write "Step 1", "Step 2". Make it clear what each step is doing.
3. Be careful with sensitive info: never include credit card data, passwords, or any other confidential info in your logs.

2

Task

SQL SELF, level 52, lesson 1

Locked

Logging a variable's value

Logging a variable's value

Open