PostgreSQL
Functions and Procedures: PL/pgSQL
An application issues 8 queries to compute a discount: order history, status, promo codes, region. Each query is a round trip over the network. One PL/pgSQL function does the same work in a single call entirely inside the database. This is not premature optimization, it is an architectural decision about where business logic lives.
- **GitLab:** project access permissions are computed via PL/pgSQL functions. One call instead of a chain of queries from Ruby cuts latency by 30 to 50 percent across 5M+ active projects.
- **Supabase:** SECURITY DEFINER functions are the core pattern for Row Level Security APIs. Users work through functions without direct access to tables.
- **Shopify:** triggers feed an audit log for every order change. In a transaction with 10+ INSERT/UPDATE statements, the trigger writes to audit_log atomically with the main data, eliminating drift risk.
PL/pgSQL: PostgreSQL's Procedural Language
PL/pgSQL is a procedural language built into PostgreSQL. It lets you write functions with variables, conditionals, loops, and exception handling. Functions run inside the server, eliminating the round-trip between application and database for each operation.
Use `CREATE OR REPLACE FUNCTION` for atomic replacement without DROP+CREATE. The function body is a plain string (dollar-quoting `$$...$$` removes the need to escape inner single quotes). Always specify `LANGUAGE plpgsql` explicitly.
GitLab stores permission calculation logic (project visibility, access levels) in PL/pgSQL functions. A single function call instead of 5 to 10 separate queries from Ruby reduces latency by 30 to 50 percent on high-traffic endpoints.
Why use dollar-quoting (`$$`) instead of regular single quotes for the function body?
Triggers: Automatic Actions on Events
A trigger is a function automatically invoked on INSERT/UPDATE/DELETE/TRUNCATE against a table. Triggers cannot be called directly, only as a reaction to an event. In PL/pgSQL, a trigger function always returns `TRIGGER` and has access to `NEW` and `OLD`.
Trigger types: BEFORE (before the operation, can modify NEW or cancel via NULL), AFTER (after, NEW/OLD are read only), INSTEAD OF (for views). FOR EACH ROW fires per row, FOR EACH STATEMENT fires once per statement.
A BEFORE UPDATE trigger function returns NULL. What happens?
RETURNS TABLE: Functions as Data Sources
`RETURNS TABLE` lets a function return a set of rows, much like a table or a VIEW, but with parameters and embedded logic. These are called Table-Valued Functions (TVF) and are used in a FROM clause like regular tables.
SQL functions (`LANGUAGE sql`) get inlined by the optimizer. Their body is spliced into the calling query and optimized as a single unit. PL/pgSQL functions are a black box to the planner. If the function is just a wrapper around a single SELECT, prefer `LANGUAGE sql`.
Why can a SQL function (`LANGUAGE sql`) run faster than an equivalent PL/pgSQL function?
SECURITY DEFINER: Run as the Creator
By default, a function runs with the caller's privileges (`SECURITY INVOKER`). `SECURITY DEFINER` changes this: the function runs with the creator's privileges. This is a convenient mechanism for Row Level Security and controlled data access.
SECURITY DEFINER without an explicit `SET search_path` is a vulnerability: an attacker can create an object with the same name in their search_path and hijack the call. Always add `SET search_path = public, pg_temp` or a stricter variant.
A function was created with SECURITY DEFINER by a superuser. Which privileges apply when a regular user calls it?
Volatility: IMMUTABLE, STABLE, VOLATILE
The volatility category tells the PostgreSQL optimizer whether the function's result can be cached and in which contexts it can be used. An incorrect volatility setting is a source of subtle bugs or performance loss.
| Category | Meaning | Caching | Examples |
|---|---|---|---|
| IMMUTABLE | Same result for the same arguments, no database access | Aggressive (pre-evaluation) | lower(), round(), md5() |
| STABLE | Same result within one query, may read the database | Within a single query | now(), current_setting(), functions with SELECT |
| VOLATILE | Result may change every call (default) | No caching | random(), nextval(), INSERT/UPDATE inside |
Any function reading data from tables must be VOLATILE, otherwise data may be stale.
STABLE is correct for functions that read data. PostgreSQL guarantees that data does not change within a single query (snapshot isolation). VOLATILE is needed only for functions that modify data or use things like `random()`.
PostgreSQL uses MVCC: each query sees a consistent data snapshot. Within one query, the result of SELECT cannot change. STABLE lets the optimizer skip repeated SELECTs when the function is called many times with the same arguments, which can yield a noticeable performance gain.
A function does SELECT from the users table and returns a user's name by ID. Which volatility category should it have?
Key Ideas
- **Function language:** `LANGUAGE sql` for simple queries (inlined by the optimizer), `LANGUAGE plpgsql` for conditionals, loops, and exception handling.
- **SECURITY DEFINER:** runs with the creator's privileges. Always add `SET search_path` to protect against path injection. Use for RLS and controlled APIs.
- **Volatility:** IMMUTABLE (pure function, usable in an index) > STABLE (reads DB, cached within a query) > VOLATILE (writes/random, no caching).
Related Topics
Functions and triggers interact with key PostgreSQL mechanisms:
- Row Level Security — SECURITY DEFINER functions are the standard way to implement an RLS API. Functions hide policy complexity from clients.
- Extensions: pg_cron — pg_cron schedules SQL functions and procedures. Functions are the primary way to encapsulate periodic tasks.
- Transactions and MVCC — STABLE volatility is grounded in MVCC snapshots. Understanding snapshot isolation explains why STABLE is correct for reading functions.
Вопросы для размышления
- An IMMUTABLE function reads from a configuration table via SELECT. What happens if the data in that table changes? Why does PostgreSQL allow this?
- An AFTER INSERT trigger on orders writes to audit_log. The INSERT into orders failed and the transaction rolled back. Does the audit_log row survive?
- get_discount() is called 1000 times in one query with the same argument. Which volatility category lets PostgreSQL skip the other 999 calls?