Stored Procedures and Procedural SQL

In the last chapter, we looked at user-defined functions: small, named pieces of logic that return a value or a result set. Functions are great when you're computing something. But a lot of real work in a database isn't about computing; it's about coordinating. "Upgrade Alice to premium" isn't a single calculation. It's a sequence: end her current subscription, insert a new one, update her tier, record the payment, and either commit all of it or none of it. That's the job of a stored procedure.

Procedures are where SQL starts looking less like a query language and more like a small programming language. You get variables, conditionals, loops, exception handling, and explicit transaction control. Most interviews don't grill you on PL/pgSQL syntax, but senior candidates are expected to know when a stored procedure is the right tool and when pushing the same logic into application code would be cleaner. We'll cover both sides.