Alright, let’s get our hands dirty. You’ve decided to write some logic inside the database itself. Smart move. This is where you stop being a mere user of the database and start becoming a master of it. PL/pgSQL is PostgreSQL’s native procedural language, and it’s the go-to for writing stored procedures and functions. It’s like SQL got a serious upgrade, gaining variables, loops, and if-then-else logic. But with great power comes great responsibility, and a few quirks you need to know about.

First, the skeleton of every PL/pgSQL function. It’s built from blocks, and the most basic block looks like this:

DECLARE
    -- This is where you declare your variables. More on this in a sec.
BEGIN
    -- This is where the magic (or the disaster) happens.
END;

Think of the DECLARE section as your backstage area. It’s where you get all your actors (variables) ready, in costume, with their names and roles clearly defined, before the play (BEGIN) starts. You can’t just introduce a new character mid-scene.

The DECLARE Section: Your Green Room

This is non-negotiable. Every variable you use must be declared here. PostgreSQL is a stickler for this. Why? Because it needs to know the data type of each variable at compile time, not runtime. This allows it to catch a whole class of errors before you even execute the thing. It’s the database’s way of saying, “Let’s not be sloppy.”

DECLARE
    customer_id INTEGER; -- Basic integer.
    customer_name VARCHAR(100); -- A string of max 100 chars.
    invoice_total NUMERIC(10,2) := 0; -- Declared AND initialized to zero.
    is_valid BOOLEAN DEFAULT FALSE; -- Another way to set a default value.
    my_record customers%ROWTYPE; -- Fancy! This declares a variable that can hold an entire row from the 'customers' table.

Best practice? Initialize your variables. Don’t just declare them and assume they’ll start as NULL or zero. Be explicit. An uninitialized variable is indeed NULL, and NULL has a nasty habit of propagating through your calculations and causing silent, mysterious failures. Trust me, debugging a NULL is far more frustrating than writing DEFAULT 0.

BEGIN and END: The Main Event

The BEGIN…END; block is your main execution stage. This is where you write your procedural logic. Notice the semi-colon after END. This is crucial. Forgetting it is like forgetting the period at the end of this sentence—everything that comes after will fail spectacularly.

The real magic, and a common pitfall, happens with nested blocks. You can have BEGIN/END inside other BEGIN/END blocks. This is useful for creating scopes for variables and exception handling.

DECLARE
    outer_variable INTEGER := 42;
BEGIN
    -- You can access outer_variable here.

    DECLARE
        inner_variable INTEGER := 100;
    BEGIN
        -- In this inner block, you can see BOTH outer_variable and inner_variable.
        outer_variable := inner_variable; -- This is perfectly valid.
    END;

    -- Now we're back in the outer block. inner_variable is now out of scope.
    -- This next line would cause a compilation error: "column "inner_variable" does not exist"
    -- outer_variable := inner_variable;
END;

This scoping is a feature, not a bug. It lets you create temporary variables for a specific task without polluting the wider namespace. Use it.

Variable Assignment: The Art of the :=

This is the part that trips up everyone who also writes SQL in other databases. In standard SQL, you use = for comparison and SET @variable = ... for assignment. PostgreSQL’s PL/pgSQL is different, and it’s a design choice I actually applaud for its clarity.

You assign a value to a variable using the := operator. Or you can use INTO after a SELECT statement.

BEGIN
    -- Method 1: The assignment operator. Simple, direct.
    customer_id := 150;
    invoice_total := 500 * 1.08; -- Calculate tax? Sure.

    -- Method 2: Using SELECT...INTO. Perfect for grabbing data from a query.
    SELECT price * quantity INTO invoice_total
    FROM order_lines
    WHERE id = 150;

    -- This is the big pitfall. What if the query returns NO rows?
    -- Then invoice_total becomes... NULL. Your value just vanished.
    -- And what if it returns MORE than one row? That's an error. Kaboom.

    -- The safer way? Use SELECT...INTO STRICT.
    SELECT price * quantity INTO STRICT invoice_total
    FROM order_lines
    WHERE id = 150;
    -- Now, if it doesn't find exactly one row, it throws an exception.
    -- This is what you want. Fail fast, fail loudly. None of this silent NULL nonsense.
END;

The INTO STRICT clause is your best friend. It turns a potentially silent data integrity bug into a loud, obvious exception that you can catch and handle. Always use it unless you have a very specific reason not to (like you’re expecting zero or multiple rows and have a plan for it).

So there you have it. The foundation. Declare everything upfront, be explicit with your values, scope your blocks wisely, and always assign with := or a strict INTO. Do this, and you’ll avoid about 80% of the beginner-level PL/pgSQL headaches. Now, let’s make these variables do something useful.