Stored procedures, functions, and triggers allow application logic to execute directly inside the database, but each object has a distinct role. A function returns a value or table and can be called from SELECT, WHERE, or joins, making it suitable for reusable computations and parameterised reporting. A procedure is invoked with CALL and is designed for multi-step maintenance or batch operations where transaction control such as COMMIT and ROLLBACK is required. Triggers are different again: they execute automatically in response to database events and are particularly useful for integrity enforcement, audit trails, and controlled row-level transformations. Choosing the correct object prevents server-side code from becoming unnecessarily complex or difficult to maintain.
SQL functions can be implemented as simple SQL expressions or with procedural languages such as PL/pgSQL when variables, branching, loops, or exception handling are required. A scalar function accepts parameters and returns one value, while a table-returning function can behave much like a parameterised view that can be joined and composed with other queries. Function volatility is also important because it communicates assumptions about how results behave: IMMUTABLE indicates that the same inputs always produce the same result without table access, STABLE allows results to remain consistent within a statement while reading database state, and VOLATILE permits results to change between calls. Declaring volatility accurately gives the query planner more information and can allow immutable functions to participate in expression indexes.
PL/pgSQL adds procedural control flow through variables, IF statements, loops, records, and exception blocks. However, the presentation strongly emphasises a set-based-first approach: a loop that performs an operation row by row is usually far slower than a single SQL statement that performs the same transformation across the entire dataset. Exception handling should likewise be deliberate. RAISE EXCEPTION can abort an operation with a clear message, while named conditions such as unique_violation can be caught when recovery is genuinely required. Broadly swallowing errors with a blanket WHEN OTHERS THEN NULL is dangerous because it can hide failures and leave data in an unexpected state.
Procedures become particularly useful for long-running maintenance tasks because they can commit work between batches. The deck demonstrates chunked archival using batches of 10,000 rows, FOR UPDATE SKIP LOCKED, and GET DIAGNOSTICS to monitor affected rows. Committing between chunks prevents a maintenance job from holding one enormous transaction and helps keep locks and write-ahead logging growth manageable. Triggers provide another form of server-side automation: BEFORE row triggers can modify or validate NEW before a row is stored, while AFTER row triggers can record what actually happened, making them well suited to audit trails. The presentation’s audit example captures inserts, updates, and deletes using TG_OP, CURRENT_USER, and JSONB snapshots of the old and new rows.
The most important lesson is not simply how to write server-side SQL, but knowing when it belongs in the database. Logic that must never be bypassed, such as integrity rules and audit trails, is a strong database-side candidate, as are set-based transformations and bulk maintenance close to the data. Frequently changing business rules, workflows involving external services, retry queues, and complex application behaviour are generally better kept in the application layer. Performance and observability also matter: row-level triggers execute once per affected row, while set-based statements can process large datasets far more efficiently. Production database code should therefore be version-controlled, tested with assertion queries, deployed through repeatable migrations, instrumented with tools such as EXPLAIN ANALYZE, and kept as set-based as possible.
DECISION STATS 