RAISE NOTICE, RETURN QUERY Statements

There are two things that really matter in programming: understanding what's going on (especially when things go sideways), and returning useful data. This is super true for PL/pgSQL, since everything runs server-side, and debugging isn't always a walk in the park. Luckily, there are built-in tools to help:

RAISE NOTICE — this is how you print messages while your function is running. Think of it like console.log in JavaScript or print in Python. You can show variable values, the current execution state, or just leave a "hello" for your future self.

RETURN QUERY — this is how you return a set of data, like a whole table or the result of a complex query. With this, PL/pgSQL functions start to feel like full-blown SQL queries.

The RAISE NOTICE Command

RAISE NOTICE lets you print messages to the screen while your function is running. The format looks like this:

RAISE NOTICE 'Message: %', value;

• % — this is a placeholder for a variable, just like printf in C.
• After the message text, you can list the variables you want to plug in.

Example Usage

Imagine you're writing a function that counts the number of students in different groups. You want to see the intermediate values to make sure everything's on track.

CREATE OR REPLACE FUNCTION count_students_in_groups() RETURNS VOID AS $$
DECLARE
    group_name TEXT;
    student_count INT;
BEGIN
    FOR group_name IN SELECT DISTINCT group_name FROM students LOOP
        SELECT COUNT(*) INTO student_count
        FROM students WHERE group_name = group_name;

        -- Print results
        RAISE NOTICE 'Group: %, Number of students: %', group_name, student_count;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

When you call this function:

SELECT count_students_in_groups();

You'll see messages like this in the logs:

NOTICE:  Group: Mathematics, Number of students: 30
NOTICE:  Group: Philosophy, Number of students: 25
NOTICE:  Group: Biology, Number of students: 18

Notice that the function doesn't return anything (it's created with RETURNS VOID), but RAISE NOTICE shows us how the loop is progressing.

Handy RAISE Tricks

Besides NOTICE, you can use other message levels too:

• RAISE DEBUG — for extra info (shows up only if the log level is set to DEBUG).
• RAISE INFO — for general info.
• RAISE WARNING — for warnings.
• RAISE EXCEPTION — for throwing errors, which we'll cover later.

For debugging, it's best to use NOTICE or DEBUG, since they're convenient and don't stop your function from running.

The RETURN QUERY Command: Return Data Like a Pro

RETURN QUERY is used in PL/pgSQL to return a set of rows. With it, you can return the result of an SQL query straight from your function. The syntax is:

RETURN QUERY <SQL-query>;

You can also combine several queries:

RETURN QUERY <SQL-query 1>;
RETURN QUERY <SQL-query 2>;

Example 1: Function with RETURN QUERY

Let's write a function that returns a list of students from a given group.

CREATE OR REPLACE FUNCTION get_students_by_group(group_name TEXT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name 
    FROM students
    WHERE group_name = group_name;
END;
$$ LANGUAGE plpgsql;

Now let's call this function:

SELECT * FROM get_students_by_group('Mathematics');

To test the function, let's first create the students table and add some data:

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
group_name TEXT NOT NULL
);

INSERT INTO students (name, group_name) VALUES
('Otto Song',     'Physics'),
('Alex Lin',      'Mathematics'),
('Anna Vel',      'Mathematics'),
('Maria Chi',     'History');

Result:

As you can see, the function works just like a regular SQL query.

Example 2: Combining Multiple Queries

What if we need to return combined data from several tables? Let's return a list of students and the courses they're enrolled in.

CREATE OR REPLACE FUNCTION get_students_and_courses()
RETURNS TABLE(student_name TEXT, course_name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT s.name, c.name
    FROM students s
    JOIN enrollments e ON s.id = e.student_id
    JOIN courses c ON e.course_id = c.id;
END;
$$ LANGUAGE plpgsql;

First, let's create three tables: students, courses, and enrollments, and then add some data:

-- Students table
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);

-- Courses table
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);

-- Enrollments table (join table)
CREATE TABLE enrollments (
student_id INT REFERENCES students(id),
course_id INT REFERENCES courses(id)
);

-- Add students
INSERT INTO students (name) VALUES
('Otto Song'),
('Alex Lin'),
('Anna Vel'),
('Maria Chi');

-- Add courses
INSERT INTO courses (name) VALUES
('Mathematics'),
('Physics'),
('History');

-- Add enrollments
INSERT INTO enrollments (student_id, course_id) VALUES
(1, 2), -- Otto -> Physics
(2, 1), -- Alex -> Mathematics
(3, 1), -- Anna -> Mathematics
(4, 3); -- Maria -> History

In this case, calling the function:

SELECT * FROM get_students_and_courses();

will give you this result:

The function neatly joins data from three tables and shows which student is enrolled in which course.

Combining RAISE NOTICE and RETURN QUERY

Sometimes RETURN QUERY and RAISE NOTICE can work together in one function, so you can keep track of what's happening and see intermediate results.

Here's an example of a function that returns student data and also prints messages to show its progress:

CREATE OR REPLACE FUNCTION debug_students()
RETURNS TABLE(student_id INT, student_name TEXT) AS $$
DECLARE
    count_students INT;
BEGIN
    -- Count the number of students
    SELECT COUNT(*) INTO count_students FROM students;
    RAISE NOTICE 'Total students: %', count_students;

    -- Return student data
    RETURN QUERY
    SELECT id, name FROM students;

    RAISE NOTICE 'Function finished running.';
END;
$$ LANGUAGE plpgsql;

If the students table isn't created yet, add it and insert some data:

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);

INSERT INTO students (name) VALUES
('Otto Song'),
('Alex Lin'),
('Anna Vel'),
('Maria Chi');

Now, when you call the function:

SELECT * FROM debug_students();

You'll get both the data and the messages:

Console output:

NOTICE:  Total students: 4
NOTICE:  Function finished running.

Common Mistakes When Using These

Variable error in RAISE NOTICE: if you forgot to declare a variable or made a typo in its name, you'll get a variable does not exist error. Always check that your variables are declared correctly.

Return type error: if you use RETURN QUERY but don't specify RETURNS TABLE when creating the function, PostgreSQL will throw an error. Make sure your return types match the data you're returning.

Placeholder error in RAISE: if the number of % placeholders doesn't match the number of variables, you'll get an error. For example:

RAISE NOTICE 'Value: %, %', value1;

This will cause an error because the second variable is missing.