PL/pgSQL Syntax Basics

Let's dive deeper into PL/pgSQL and start using it more actively.

Code Block

A code block in PL/pgSQL is the main building block of the language. You could say it's the skeleton that holds up our functions, procedures, and all that magic. The block handles logic execution, data processing, error handling, and all of that in one "container".

PL/pgSQL blocks are structured and include three main parts:

1. DECLARE: variable declaration (optional).
2. BEGIN ... END: the main execution block, where the logic happens.
3. EXCEPTION: error handling (optional).

If you like analogies: imagine a recipe. While the recipe text might start with a list of ingredients, the real magic happens in the cooking process itself. In PL/pgSQL terms:

• DECLARE — that's your list of ingredients (variables).
• BEGIN ... END — that's where you mix, fry, and boil stuff.
• EXCEPTION — that's your backup plan if something burns.

PL/pgSQL Block Syntax

First, let's look at the general structure of a block, like a "skeleton". Later we'll add the meat (or veggie cheese, if that's your thing) — the actual logic.

DO $$
DECLARE
    -- Variables are declared here
    student_count INT;
BEGIN
    -- Logic goes here
    SELECT COUNT(*) INTO student_count FROM students;
    RAISE NOTICE 'Total number of students: %', student_count;
EXCEPTION
    -- Errors are handled here
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred.';
END;
$$;

Let's break this down step by step.

1. DECLARE — this is where we declare our variables. The cool thing is, PL/pgSQL supports almost all data types available in PostgreSQL — from humble INTEGER to exotic JSONB. To declare a variable, just give it a name, a data type, and if you want, an initial value.

Example:

DECLARE
    student_name TEXT;    -- Variable for the student's name
    course_count INT := 0; -- Set initial value to 0
    is_graduated BOOLEAN; -- Boolean variable

Notice that variables can be initialized (like course_count) or not.

2. BEGIN ... END — the main execution block.

This part of the block is responsible for running the main logic. Here we can:

• Run SQL queries (SELECT, INSERT, etc.).
• Manipulate data.
• Use control structures (IF, LOOP, etc.).
• Print debug messages with RAISE.

Example:

BEGIN
    SELECT COUNT(*) INTO student_count FROM students;
    IF student_count > 0 THEN
        RAISE NOTICE 'We have students!';
    ELSE
        RAISE NOTICE 'No students found.';
    END IF;
END;

3. EXCEPTION — error handling (optional).

If an error happens while running the block, the EXCEPTION section lets us catch it and do something useful — like print a message or run some alternative code.

Example:

BEGIN
    SELECT COUNT(*) INTO student_count FROM non_existing_table; -- Error!
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Oops, something went wrong!';
END;

Real Example: Counting Students

Now let's put all the parts together in an example you might actually use. We'll write a PL/pgSQL block that counts the number of students in the students table and prints a message.

DO $$
DECLARE
    total_students INT; -- Variable to store the number of students
BEGIN
    -- Count the number of students
    SELECT COUNT(*) INTO total_students FROM students;

    -- Print the result message
    RAISE NOTICE 'Number of students: %', total_students;
EXCEPTION
    -- Handle possible errors, like if the table doesn't exist
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred while counting students.';
END;
$$;

Running this block will print a message to the console. For example: Number of students: 42.

Variable Usage Tips

Let's go over a few important points:

Assigning values to variables. To put data into a variable, you can use the SELECT INTO operator:

SELECT COUNT(*) INTO total_students FROM students;

Variable initialization. If you didn't assign a value to a variable when declaring it, its default value will be NULL.

For example:

DECLARE
    my_var INT; -- Value is NULL

Variables of type RECORD. This is a universal variable type that can hold rows from a table. Example:

DECLARE
    student RECORD;
BEGIN
    SELECT * INTO student FROM students WHERE id = 1;
    RAISE NOTICE 'Student Name: %, Age: %', student.name, student.age;
END;

Example: Counting Courses for a Student

Now let's solve a practical task: count how many courses a student is enrolled in and print the result.

DO $$
DECLARE
    student_id INT := 1;   -- Student ID
    course_count INT;      -- Variable for the number of courses
BEGIN
    -- Count the number of courses
    SELECT COUNT(*) INTO course_count
    FROM enrollments
    WHERE student_id = student_id;

    -- Print the message
    RAISE NOTICE 'Student ID % is enrolled in % courses.', student_id, course_count;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred while processing student ID %', student_id;
END;
$$;

This block is flexible: you can change student_id to check how many courses different students are enrolled in.

Errors and How to Avoid Them

If the PL/pgSQL inside you is already bouncing around like a tiny hot dog in a microwave, that's totally normal. At first, you'll probably run into some "classic" mistakes. Here are a few examples:

Forgetting to declare a variable. If you forgot to declare a variable with DECLARE, PL/pgSQL will throw an error saying the variable "does not exist".

Trying to use NULL as a value. If a variable was declared but not assigned a value, it'll be NULL. This can cause weird behavior. For example:

IF my_var = NULL THEN -- WON'T work!

Use IS NULL instead:

IF my_var IS NULL THEN

Incorrect use of the EXCEPTION section. Sometimes devs catch all errors (WHEN OTHERS) but don't write what to do. This can hide the real problem. It's better to log the error message:

RAISE NOTICE 'Error: %', SQLERRM;