BrevFeed
Sign in

37. PL/pgSQL basics: going beyond plain SQL

📖 Reading · 6 min

Why go beyond plain SQL at all

Every query so far has been declarative — you describe the result you want, the planner figures out how. That model breaks down for genuine procedural logic: loops, conditional branching across multiple statements, reusable named logic with parameters. PL/pgSQL ("Procedural Language/PostgreSQL") is Postgres's built-in procedural language, deliberately similar to Oracle's PL/SQL (recall Module 0's point that each database speaks its own dialect), used to write functions and stored procedures.

A minimal function

CREATE FUNCTION film_count_by_rating(p_rating mpaa_rating)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    result integer;
BEGIN
    SELECT count(*) INTO result FROM film WHERE rating = p_rating;
    RETURN result;
END;
$$;

SELECT film_count_by_rating('PG');

Anatomy: - CREATE FUNCTION name(params) RETURNS type — the signature, just like any language. - LANGUAGE plpgsql — tells Postgres which procedural language the body is written in (Postgres supports several — SQL, PL/pgSQL, PL/Python, others — PL/pgSQL is the default, most common choice). - $$ ... $$ — dollar quoting, Postgres's way of delimiting a block of text (the function body) without worrying about escaping internal single quotes — genuinely necessary since the body itself will usually contain SQL strings quoted with '. - DECLARE — variable declarations, before BEGIN. - BEGIN ... END — the actual procedural body (this BEGIN is PL/pgSQL block syntax, not a SQL transaction BEGIN — same keyword, different meaning, distinguished entirely by context). - SELECT ... INTO variable — the standard way to pull a query result into a PL/pgSQL variable. - RETURN — sends back the function's result.

Variables and types

DECLARE
    v_count      integer;
    v_title      text;
    v_rate       numeric(4,2) := 0;      -- := is assignment, with a default
    v_customer   customer%ROWTYPE;        -- a variable shaped like a whole customer row
    v_email      customer.email%TYPE;     -- a variable typed to MATCH a specific column exactly

%ROWTYPE and %TYPE are worth using deliberately: a variable declared customer.email%TYPE automatically tracks the real column's type — if email is ever widened or its type changed, this variable definition never needs to change to match. This is a small but genuine maintainability win over hardcoding text and hoping it stays accurate.

Control flow: IF and loops

CREATE FUNCTION rate_tier(p_rate numeric)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_rate < 1.00 THEN
        RETURN 'Budget';
    ELSIF p_rate < 3.00 THEN
        RETURN 'Standard';
    ELSE
        RETURN 'Premium';
    END IF;
END;
$$;

(Notice this is the exact logic of Module 1's CASE expression, rewritten procedurally — worth comparing directly: a CASE expression is almost always the better choice for something this simple, since it stays inline in ordinary SQL without the overhead of a function call. PL/pgSQL earns its keep for logic CASE genuinely can't express — loops, multiple statements, side effects.)

CREATE FUNCTION total_films_processed(p_limit integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    v_film RECORD;
    v_count integer := 0;
BEGIN
    FOR v_film IN SELECT film_id, title FROM film ORDER BY film_id LIMIT p_limit LOOP
        -- (imagine real per-row work here; this loop just counts)
        v_count := v_count + 1;
    END LOOP;
    RETURN v_count;
END;
$$;

FOR variable IN query LOOP ... END LOOP is the standard way to iterate row-by-row in PL/pgSQL — genuinely necessary sometimes, but worth flagging directly: row-by-row procedural loops are almost always slower than an equivalent set-based SQL statement, and this course has spent nine modules building exactly the set-based thinking that usually makes a loop unnecessary. Reach for a loop when the per-row logic truly can't be expressed as one declarative statement (calling an external function per row, complex branching that genuinely differs row to row) — not as a default translation of "for each row, do X."

Check yourself

  1. What's the difference between the BEGIN inside a PL/pgSQL function body and the BEGIN that starts a SQL transaction?
  2. What does declaring a variable as customer.email%TYPE buy you over just declaring it text?
  3. Why does this lesson recommend against reaching for a FOR ... LOOP as a default way to process rows?
← Previous Next →