PL/pgSQL Blocks

Master PL/pgSQL block structure — anonymous DO blocks, named blocks via CREATE FUNCTION, nested blocks with labels, declaration sections, and best practices. Worked examples include a fixed orders/revenue calculation, the add_numbers function, and outer/inner nested blocks.

In PL/pgSQL, a block is the fundamental unit of code. Everything you write — anonymous scripts, functions, procedures, triggers — is structured around the BEGIN ... END block. Blocks group related statements, scope variables to where they're needed, and mark the boundaries for exception handling.

Two big categories you'll encounter:

The basic syntax:

Examples:

-- With initial value
counter int = 0;

-- Without initial value (defaults to NULL)
max int;

-- Both := and = work for assignment
total numeric := 100.50;
quantity int = 5;

An anonymous block is PL/pgSQL code that runs once, immediately, with no name and no stored definition. You wrap it in DO $$ ... $$;. Useful for maintenance tasks, data migrations, ad-hoc analysis — anything you wouldn't want to clutter the database catalog with a permanent function for.

DO $$
BEGIN
    RAISE NOTICE 'This is an anonymous block';
END
$$;

A more substantial example — calculate revenue from the past month using SELECT INTO to capture a query result, then format it with RAISE NOTICE. First, the sample table:

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    order_date   DATE,
    order_amount NUMERIC
);

INSERT INTO orders (order_id, order_date, order_amount) VALUES
    (1, '2024-03-01',  500),
    (2, '2024-03-05',  700),
    (3, '2024-03-10',  900),
    (4, '2024-03-15',  400),
    (5, '2024-03-20',  600),
    (6, '2024-03-25',  800),
    (7, '2024-03-30', 1000),
    (8, '2024-04-05', 1200);

Now the anonymous block that uses the data:

DO $$
DECLARE
    total_revenue NUMERIC := 0;
BEGIN
    SELECT SUM(order_amount)
    INTO   total_revenue
    FROM   orders
    WHERE  order_date >= CURRENT_DATE - INTERVAL '1 month';

    RAISE NOTICE 'Total revenue generated in the last month: $%', total_revenue;
END
$$;

The exact total depends on when you run the example — CURRENT_DATE - INTERVAL '1 month' evaluates relative to today. With test data dated March 2024 and an April 2024 row, the result varies by current date.

Named blocks — PL/pgSQL functions and procedures — are stored in the database and can be called by name. The syntax for a function:

A function that takes two integers and returns their sum:

CREATE OR REPLACE FUNCTION add_numbers(num1 INTEGER, num2 INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER;
BEGIN
    result := num1 + num2;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Call it
SELECT add_numbers(10, 20);

Reuse the orders table from earlier. Wrap the count-of-orders logic in a named function:

CREATE OR REPLACE FUNCTION calculate_total_orders()
RETURNS INTEGER AS $$
DECLARE
    total_orders INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO   total_orders
    FROM   orders;

    RETURN total_orders;
END;
$$ LANGUAGE plpgsql;

SELECT calculate_total_orders();

The function encapsulates the logic — any other code that needs the count just calls calculate_total_orders() instead of repeating the SELECT. If the counting rule changes (e.g. exclude cancelled orders), update the function once and every caller benefits.

A block can contain another block — a subblock. Nested blocks have their own DECLARE sections, so variables declared inside are scoped to the inner block only. Useful for organizing logic and limiting variable lifetime.

Two blocks, both labelled, with the inner one referencing a variable from the outer:

DO $$
<>
DECLARE
    x int = 0;
BEGIN
    x := x + 1;

    <>
    DECLARE
        y int = 2;
    BEGIN
        y := y + x;
        RAISE NOTICE 'x=% y=%', x, y;
    END inner;
END outer;
$$;

Step by step:

1. Outer block declares x = 0, then increments to 1
2. Inner block declares y = 2, then adds x (which is 1) → y = 3
3. RAISE NOTICE prints x=1 y=3

Subblocks get a more thorough treatment in the dedicated Nested Block page — including how variable shadowing works when an inner block reuses an outer block's name.

1. Use DO for one-time tasks; create named functions for reusable logic. Don't pollute the catalog with single-use functions.
2. Always specify LANGUAGE plpgsql on CREATE FUNCTION. Without it, PostgreSQL defaults to SQL, and your PL/pgSQL syntax will fail to parse.
3. Use CREATE OR REPLACE FUNCTION for development — makes your script idempotent. Skip the explicit DROP/CREATE dance.
4. Label outer blocks when nesting — they let inner blocks unambiguously refer to outer variables.
5. Match END label to BEGIN label — END outer; vs END inner;. Optional but catches mistakes when blocks get long.
6. Initialize variables explicitly when the default NULL would cause subtle bugs (e.g. NULL + 5 = NULL).
7. Keep blocks focused. If a function is doing five things, consider splitting it.

• A PL/pgSQL block has up to three sections: DECLARE (optional), BEGIN/END (required), EXCEPTION (optional).
• Anonymous blocks run via DO $$ ... $$; — one-shot, not stored. Useful for migrations, ad-hoc tasks.
• Named blocks live as functions or procedures created with CREATE FUNCTION / CREATE PROCEDURE. Reusable, callable from anywhere.
• Variable declarations: name type = initial_value; — both = and := work for assignment.
• Subblocks are blocks nested inside other blocks. They have their own scope; inner blocks can reach outer variables but outer blocks can't see inner ones.
• Always end CREATE FUNCTION with LANGUAGE plpgsql — without it, PostgreSQL assumes plain SQL.