Sometimes logic belongs close to the data. Computing a complex aggregate, enforcing a multi-step invariant, or processing rows in a loop can be far faster inside the database than shuttling data back and forth to an application over the network. PostgreSQL lets you write this server-side logic as functions and procedures, most commonly in PL/pgSQL, its built-in procedural language.
PL/pgSQL adds variables, control flow, loops, and exception handling on top of SQL. A function takes arguments, runs a body, and returns a value, a set of rows, or a table; it can be called from queries and composed like any expression. A procedure is similar but can manage transactions — committing and rolling back within its body — which functions cannot.
Server-side code is a sharp tool: it reduces round-trips, keeps related logic atomic, and centralises rules the database itself should enforce. But it can also hide business logic away from application developers and complicate deployment. This lesson covers how to write functions and procedures well, and when putting logic in the database is the right call.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.