advanced~4h

Advanced SQL — Stored Procedures, Functions, Triggers & Sequences

Functions, procedures, triggers, and sequences let you push logic and guarantees into the database itself, so they hold true no matter which application or code path touches the data.

Learning objectives

Explain the structural difference between a function and a stored procedure, and why only one of them can manage its own transaction.
Describe exactly when a BEFORE trigger's return value matters versus an AFTER trigger's.
Explain why sequences guarantee uniqueness but not gaplessness, and why that tradeoff is unavoidable under concurrency.

Some guarantees ("this side effect always happens on this table event, no matter which application touches it") can only be enforced reliably by pushing logic into the database itself — functions, procedures, and triggers are how SQL lets you do that, and sequences are how it guarantees unique IDs under real concurrency.

Logic that lives only in application code has to be correctly re-implemented in every single place that touches the data — the web app, the batch job, the admin tool, a future integration nobody's built yet. A trigger or procedure guarantees the logic runs regardless of which client made the change.

Function — always returns a value, runs inside the caller's transaction. Stored procedure — invoked via CALL, can manage its own commits, doesn't have to return a value. Trigger — a function attached to a table event (BEFORE/AFTER INSERT/UPDATE/DELETE) that fires automatically. Sequence — a database object generating guaranteed-unique (but not necessarily gapless) integers, typically backing auto-incrementing primary keys.

📖 Story

Imagine a warehouse where every time stock is adjusted, three things must always happen together: update the inventory count, write an audit-log row, and check whether it's dropped below a reorder threshold. If that logic lives only in application code, every single place that touches inventory — the web app, the nightly batch job, the support team's admin tool — has to remember all three steps, correctly, forever. Triggers, functions, and stored procedures let you push logic like this into the database itself, so it runs no matter which application touched the data.

Functions vs. stored procedures

A function always returns a value (or a set of rows) and can be used inside a SELECT, WHERE, or anywhere an expression is expected. A stored procedure is invoked directly with CALL, doesn't have to return anything, and — critically — can manage its own transactions internally (COMMIT/ROLLBACK inside the procedure body), which a function cannot do.

Triggers: code that runs automatically on a table event

A trigger attaches a function to a table event — BEFORE/AFTER an INSERT, UPDATE, or DELETE — so that function runs automatically, without the application ever calling it directly. A BEFORE trigger can inspect and modify the row before it's written; an AFTER trigger runs once the write has already happened, typically used for side effects like audit logging.

Sequences: guaranteed-unique, gapless-adjacent counters

A sequence is a database object that hands out a new integer every time it's asked (nextval()), used almost universally to generate primary key values (SERIAL/BIGSERIAL/IDENTITY columns are sequences under the hood). Sequences are safe under massive concurrency by design — many transactions can call nextval() simultaneously and each gets a distinct value — but that safety comes at the cost of allowing gaps: a rolled-back transaction's sequence value is never reused.

	Returns a value?	Called how	Manages own transactions
Function	Always	Inside a query expression	No
Stored Procedure	Optional	`CALL procedure_name(...)`	Yes
Trigger	N/A	Automatically, on a table event	Runs inside the triggering statement's transaction

How a trigger actually fires

When you INSERT/UPDATE/DELETE a row, PostgreSQL checks the table's trigger catalog (pg_trigger) for any matching BEFORE/AFTER trigger, and if found, calls the attached trigger function automatically, passing it the OLD and NEW row versions as special variables. A BEFORE trigger's return value actually replaces the row that gets written (or returning NULL cancels the write entirely); an AFTER trigger's return value is ignored, since the write has already committed to happen.

Why a stored procedure can COMMIT but a function can't

PostgreSQL functions execute as part of the calling statement's transaction — they inherit it, and cannot start or end one. Procedures, invoked via CALL, run as their own top-level statement and are explicitly allowed to issue COMMIT/ROLLBACK inside their own body, which is genuinely useful for long-running batch operations that need to commit progress incrementally rather than holding one enormous transaction open.

How a sequence stays safe under concurrency without locking

nextval() doesn't take a normal row lock — it uses a lightweight, dedicated increment operation that's never rolled back, even if the surrounding transaction is. This is precisely why sequences can have gaps: if a transaction calls nextval() and then rolls back, that value is gone forever, never reused, because guaranteeing "no gaps" would require blocking every other concurrent nextval() call until the first transaction resolves.

Write the logic first as a plain SQL function if all it needs to do is compute and return a value: CREATE FUNCTION calc_discount(price numeric) RETURNS numeric AS $$ ... $$ LANGUAGE plpgsql;.
If the logic needs to manage its own commit points (e.g., a nightly batch that commits every 1,000 rows processed), write it as a procedure instead: CREATE PROCEDURE process_batch() LANGUAGE plpgsql AS $$ ... $$;, invoked with CALL process_batch();.
If the logic must run automatically whenever a table changes — regardless of which client makes the change — write a trigger function (RETURNS trigger), then attach it: CREATE TRIGGER trg_audit AFTER INSERT ON inventory FOR EACH ROW EXECUTE FUNCTION log_inventory_change();.
For any table needing auto-incrementing IDs, either use GENERATED ALWAYS AS IDENTITY (the modern standard) or an explicit CREATE SEQUENCE, and reference it in the column default.
Test the trigger/function/procedure under concurrent writes specifically, not just a single sequential test — this is where most bugs in database-side logic actually surface.

Reach for a trigger only when the logic genuinely must run regardless of which application or code path touches the table — otherwise, keep the logic in application code where it's easier to test, version, and debug.
Prefer GENERATED ALWAYS AS IDENTITY over a manually created SEQUENCE for new primary key columns — it ties the sequence's lifecycle to the column automatically, avoiding orphaned sequences.
Keep trigger functions small and fast — a slow BEFORE trigger adds its full execution time to every single write against that table, for every caller, forever.
Use a stored procedure (not a function) specifically when you need incremental commits inside a long-running batch operation — that's the one thing a function structurally cannot do.
Log or comment, directly in the trigger's definition, exactly why it exists — a trigger is invisible from application code, and the next engineer reading the app won't know it's running at all unless the database layer documents itself.

⚠️ Why this keeps happening

Triggers and stored procedures are invisible from the application code that triggers them — a developer reading the app layer has no way to know extra logic runs in the database at all, unless they specifically go looking at the schema, which is exactly why bugs involving them are so often mysterious.

Writing a slow BEFORE trigger on a hot table. Every single INSERT now pays that trigger's full execution cost, and because it's invisible from the application, nobody thinks to look there when write latency creeps up.
Assuming sequences never have gaps. A rolled-back transaction's nextval() call is gone forever — code that assumes IDs are perfectly sequential with no gaps (e.g., using ID gaps to detect "missing" rows) is built on a false assumption.
Forgetting a function can't COMMIT. Trying to add transaction-control statements inside a LANGUAGE plpgsql function (not a procedure) is a hard error — the fix is converting it to a procedure, not working around it inside the function.
Stacking triggers on the same table across teams without cross-team visibility, leading to a write that silently cascades through three unrelated triggers nobody on the team touching the code even knows exist.
Not testing trigger logic under concurrent writes. A trigger that works fine in a single-session test can behave completely differently once two sessions fire it simultaneously against the same row.

Keep BEFORE triggers on frequently written tables as lightweight as possible — their cost is paid on every single write, multiplying across your highest-traffic tables specifically.
Prefer set-based logic inside a function/procedure over row-by-row loops wherever possible — FOR EACH ROW loops inside plpgsql lose the query planner's ability to optimize the operation as a whole.
Batch commits inside a long-running stored procedure (commit every N rows) rather than holding one multi-hour transaction open — long transactions block vacuum and hold locks far longer than necessary.
Avoid triggers that themselves fire more triggers (cascading side effects) on high-throughput tables — each hop multiplies total write latency, often invisibly.
Cache sequence values in batches for very high-throughput inserts (CACHE clause on CREATE SEQUENCE) if nextval() contention itself becomes measurable — rare, but real under extreme insert rates.

Grant EXECUTE on functions/procedures deliberately per role, exactly like table grants — a function is a capability (it can do anything its body is allowed to do, including writes a caller's own role might not directly have), and should be scoped to who genuinely needs it.

Since triggers are invisible from application code, monitor write latency per table after adding any new trigger specifically — a slow trigger silently adds its full cost to every write against that table, and nothing in application-level monitoring will point you there directly.

Document every trigger's existence and purpose somewhere visible to the whole team (schema comments, a data dictionary, or a README) — invisibility is the core operational risk of triggers, and documentation is the direct countermeasure.
Monitor write latency per table specifically after adding any new trigger, to catch a slow trigger before it becomes a systemic bottleneck.
Version-control every function/procedure/trigger definition through migrations, exactly like table schema — a trigger changed directly in production outside of migration history is a common source of environment drift.
Alert on stored procedures that run as long-lived batch jobs failing partway through — since they may have already committed some batches, a naive retry-from-scratch can double-process the already-committed portion.
Grant EXECUTE on functions/procedures deliberately per role, the same way you'd grant table access — a function is a capability, and capabilities should be scoped to who actually needs them.

Write a plain SQL function that computes a discounted price and use it directly inside a SELECT's column list.
Write a trigger function that logs every UPDATE on a table's price column into a separate price_audit table, then confirm it fires automatically on a plain UPDATE statement.
Write a stored procedure that processes rows in batches of 100, committing after each batch, and confirm (via a deliberate mid-run failure) that already-committed batches survive a rollback of the remaining work.
Create a table with a GENERATED ALWAYS AS IDENTITY column, insert several rows with one insert deliberately rolled back, and confirm the resulting IDs have a visible gap.

✓ Quick recap

A function always returns a value and runs inside the caller's transaction; a procedure can manage its own commits and is invoked with CALL.
A trigger attaches a function to a table event (BEFORE/AFTER INSERT/UPDATE/DELETE) so it fires automatically, regardless of which client wrote the data.
Sequences guarantee uniqueness under concurrency, not gaplessness — a rolled-back transaction's value is gone forever.
Prefer GENERATED ALWAYS AS IDENTITY over a manually managed sequence for new primary keys.
Document triggers and procedures explicitly — they're invisible from application code, which is exactly what makes their bugs hard to trace.

Want a visual for this concept?

Generate a diagram tailored to “Advanced SQL — Stored Procedures, Functions, Triggers & Sequences” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to SQL Fundamentals Deep Dive — Data Types, NULL Handling & Query Execution Order →← Back to all SQL chapters