PostgreSQL Stored Procedures

23 steps in 6 sets of SQL.

Everything else in the SQL corpus runs on SQLite; this tour crosses to PostgreSQL for the one thing SQLite deliberately does not do — code that lives in the database. Functions the planner can fold into queries, PL/pgSQL blocks with variables and control flow, procedures that commit their own batches, and triggers that make a table enforce its own rules: the tools of teams that treat the database as a participant rather than a filing cabinet.

The tour runs from CREATE FUNCTION through the PL/pgSQL language to procedures, query-driven code, and error handling: volatility promises, %TYPE declarations, SELECT INTO with FOUND, RETURN QUERY, dynamic SQL with format(), RAISE at every volume, and trigger functions reading NEW and OLD. The encore ships an audit trail the application cannot forget to write, a batch restock procedure, and a report served two ways from one function. Every seed executes against a real PostgreSQL.

Start this tour

Functions

CREATE FUNCTIONA saved expression the planner can call anywhere a value fits.
Defaults and named argumentsA tax function called three ways: by default, by position, and by name.
Table functionsRETURNS TABLE turns a function into something you can put in FROM.
IMMUTABLE and STABLEVolatility is a promise to the planner about what repeat calls may return.

PL/pgSQL blocks

DO blocksAn anonymous PL/pgSQL block: declare, query, and report, all inline.
Variables and %TYPEDeclare types by hand, or borrow a column's type so they never drift apart.
IF and ELSIFA stock verdict picked by walking conditions in order until one is true.
LoopsFOR counts up or down over a range; EXIT WHEN leaves early.

Procedures & transactions

CREATE PROCEDUREA procedure is CALLed for its effects — here, cancelling an order.
INOUT parametersA payment procedure that hands a receipt back through its parameter list.
COMMIT inside a procedureOnly procedures may commit mid-run, releasing work batch by batch.
OR REPLACE and DROPFunction lifecycle: create it, replace the body in place, drop it by signature.

Queries in code

SELECT INTO and FOUNDOne row lands in a variable, and FOUND says whether anything was caught.
RETURN QUERYA PL/pgSQL function that streams a whole result set back out.
FOR over query rowsVisit each order as a record: the loop variable holds one row at a time.
EXECUTE and format()SQL built at run time, with format() doing the quoting a paste-up would botch.

Errors & triggers

RAISE levelsOne message, several volumes: DEBUG stays silent, NOTICE informs, WARNING nags.
ASSERTA tripwire for invariants: fail loudly the moment the impossible happens.
EXCEPTION blocksDivision by zero, caught by condition name; the block's work rolls back first.
Trigger functionsA stock floor enforced by the table itself: the trigger runs on every update.

Encore

audit_triggers.sqlA price-change audit the application cannot forget to write: a trigger writes it.
restock_procedure.sqlA nightly restock procedure: find the low shelves, top up, commit as you go.
report_function.sqlOne table function feeds a query and a narrated report from the same numbers.