PL/pgSQL — Postgres's Procedural Language

mysql3-8 already covered MySQL's own stored procedures, and MySQL's procedural dialect genuinely does support variables, loops, and conditionals — this isn't a "MySQL can't do this" chapter. What's genuinely different is Postgres's own procedural architecture, ergonomics, and a real security gotcha worth tying directly back to sqli1.

PL/pgSQL — A Real Procedural Language, Not Just SQL Extensions

PL/pgSQL adds real programming constructs — variables, IF/CASE, loops, structured exception handling — directly around SQL statements inside a function or procedure body. The genuine architectural difference from MySQL isn't procedural capability itself; it's that Postgres supports multiple pluggable procedural languages for writing functions — PL/pgSQL is the default and most common, but PL/Python, PL/Perl, and PL/Tcl are also available as real, first-class options. MySQL has only its own single built-in procedural SQL dialect, with no comparable multi-language extensibility.

Functions vs. Procedures

Postgres distinguishes a function (returns a value, usable directly inside a query — SELECT my_func(x)) from a procedure (invoked via CALL, able to manage its own transactions with COMMIT/ROLLBACK inside it, with no requirement to return a value). MySQL has both CREATE FUNCTION and CREATE PROCEDURE too, so this distinction itself isn't unique to Postgres — but Postgres functions tend to integrate more fluidly into ordinary SQL, callable directly inside a SELECT list or a WHERE clause exactly like a built-in function.

A Worked Example — A Function

CREATE FUNCTION total_order_value(order_id_param INT)
RETURNS NUMERIC AS $$
DECLARE
  total NUMERIC;
BEGIN
  SELECT SUM(quantity * unit_price) INTO total
  FROM order_items
  WHERE order_id = order_id_param;

  RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;

SELECT total_order_value(42);

The $$ ... $$ around the function body is dollar-quoting — a genuine Postgres-specific convenience letting a multi-line body be written without escaping internal quotes. It's a small but real ergonomic win: MySQL's own DELIMITER // convention exists specifically because semicolons inside a stored procedure body would otherwise be misread as the end of the outer CREATE PROCEDURE statement, forcing a temporary delimiter change and a matching // at the end. Dollar-quoting sidesteps that whole dance entirely.

Triggers

Postgres triggers call a separately-defined trigger function — written in PL/pgSQL, using a special TRIGGER return type and the special NEW/OLD record variables. A common real-world example: an auto-updating updated_at column.

CREATE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Because the trigger logic lives in a separately-defined, reusable function, that same set_updated_at() function can be attached to any number of other tables' own triggers without rewriting the logic each time. MySQL instead defines the trigger body directly inline inside the CREATE TRIGGER statement itself, one-to-one with a single trigger — a real reusability difference.

Exception Handling

PL/pgSQL supports structured exception handling with a BEGIN ... EXCEPTION WHEN ... END block, catching specific error conditions (like unique_violation) and responding to them within the function. MySQL's own DECLARE ... HANDLER approach is functionally comparable — both engines really do support real exception handling — the two are simply syntactically different rather than one being categorically more capable.

Hands-On Exercises