PostgreSQL Functions & Triggers: PL/pgSQL & Stored Procedures

Q: What are BEFORE, AFTER, and INSTEAD OF triggers?

They differ by when they fire. A BEFORE trigger runs before the change is applied and can validate or modify the row being written (or reject it) — ideal for setting timestamps or defaults. An AFTER trigger runs after the change and is used to react — audit logging, cascading updates, notifications. An INSTEAD OF trigger applies to views, translating a change on the view into changes on underlying tables to make the view writable.

PostgreSQL Functions & Triggers

PostgreSQL can run real logic inside the database. Functions (written in PL/pgSQL or other languages) encapsulate reusable queries and procedural code; triggers run functions automatically in response to data changes; and stored procedures run multi-statement operations with transaction control. Together they let you push logic close to the data — for atomicity, consistency, and performance — instead of round-tripping everything through the application.

This power is double-edged. In-database logic can guarantee invariants no application code path can bypass and eliminate chatty round trips, but it can also become hidden, hard-to-test business logic scattered across triggers that surprise the next developer. The skill is knowing what genuinely belongs in the database (integrity, data-adjacent operations, performance-critical logic) versus what belongs in the application (business rules, orchestration, anything that benefits from your app's tooling). This page maps that.

TL;DR

Functions (usually PL/pgSQL) encapsulate reusable logic and queries callable from SQL; procedures add multi-statement transaction control (COMMIT/ROLLBACK inside).
Triggers run a function automatically on INSERT/UPDATE/DELETE — BEFORE (validate/modify the row), AFTER (react/cascade), or INSTEAD OF (on views).
Great for data integrity, auditing, updated_at timestamps, denormalization upkeep, and cutting round trips for data-heavy operations.
Keep core business logic in the application where it's testable, versioned with your code, and visible — not buried in triggers.
Triggers are implicit: they fire invisibly, so overusing them creates surprising, hard-to-debug behavior.
Functions run within the transaction that calls them, so their effects are atomic with it.

Quick Example

A classic, justified use: a trigger that keeps an updated_at column current automatically, so no application code can forget to.

The database guarantees updated_at is correct regardless of which application, script, or manual query does the update — an invariant no code path can bypass.

Core Concepts

Functions

A function is named, reusable code callable from SQL. The default procedural language is PL/pgSQL (SQL plus variables, control flow, loops, exception handling), but Postgres also supports plain SQL functions and, via extensions, other languages (PL/Python, PL/v8). Functions can return scalars, rows, or sets, and they execute within the calling transaction — their work commits or rolls back atomically with it.

Procedures vs Functions

Introduced to complement functions, stored procedures (CREATE PROCEDURE, called with CALL) can manage transactions internally — issuing COMMIT/ROLLBACK between statements — which functions cannot. Use a procedure for multi-step operations that need their own transaction control (large batch jobs processing in chunks); use a function for computing and returning values within the caller's transaction.

Triggers

A trigger binds a function to a table event so it runs automatically:

Triggers can fire FOR EACH ROW (per affected row) or FOR EACH STATEMENT (once per statement). A BEFORE ... FOR EACH ROW trigger can even change or reject the row being written by modifying or returning NEW (or NULL to skip it).

The Implicit-Behavior Tradeoff

Triggers are powerful precisely because they're automatic — and dangerous for the same reason. Logic in a trigger fires invisibly on every relevant change, so a developer reading the application code may have no idea it's happening. A web of triggers (one updating a column, which fires another trigger, which cascades further) becomes very hard to reason about and debug. This implicitness is the core reason to use triggers sparingly and for the right things.

What Belongs in the Database vs the App

A practical division:

Good in the database (functions/triggers):

Data integrity guarantees that must hold no matter what writes the data — constraints beyond CHECK, maintaining invariants, enforcing rules centrally.
Auditing / history — AFTER triggers writing change logs, so nothing escapes the audit trail.
Housekeeping — updated_at timestamps, maintaining denormalized/aggregate columns, search tsvector upkeep.
Data-heavy operations — set-based logic that would otherwise be many app round trips; keeping the computation next to the data.

Better in the application:

Core business logic and workflows — where it's testable, versioned with your code, debuggable, and visible to the team.
Orchestration and external calls — anything coordinating services, APIs, or side effects beyond the database.
Logic that changes often — application code with your normal deploy/test pipeline is easier to evolve than database functions.

💡 Tip: A good rule of thumb: use the database to protect data (integrity, audit, housekeeping) and the application to make decisions (business rules, workflows). When in doubt, keep business logic in the app where your tests and tooling live.

Best Practices

Reserve Triggers for Integrity and Housekeeping

Use triggers for the invariants and bookkeeping that must be guaranteed at the data layer (updated_at, audit logs, maintaining derived columns). Resist putting branching business logic in triggers — it becomes hidden behavior the app team can't see or easily test.

Keep Functions Small and Documented

Treat in-database functions like any code: keep them focused, comment their purpose, and manage them in version control via migrations — not created ad hoc in production. Undocumented functions accumulated over years become untouchable legacy.

Version Functions With Migrations

Database logic must live in your migration history, reviewed and deployed like schema changes, so it's reproducible across environments and its evolution is tracked. Hand-editing functions directly in a database is how environments drift.

Beware Trigger Cascades and Performance

Triggers firing triggers, or a FOR EACH ROW trigger on a bulk operation running millions of times, can devastate performance and create baffling behavior. Prefer statement-level triggers for bulk-affecting logic where possible, and map out what fires what before adding a new trigger.

Handle Errors Deliberately

Since functions run in the caller's transaction, an unhandled error rolls back the whole thing. Use PL/pgSQL exception handling where appropriate, and be intentional about whether a trigger should reject the operation (raise an exception) or silently adjust it.

Common Mistakes

Business Logic Buried in Triggers

Row-Level Triggers on Bulk Operations

A FOR EACH ROW trigger on a statement that touches millions of rows runs millions of times — often unexpectedly catastrophic for performance. Use statement-level triggers, or handle bulk logic in set-based SQL, where feasible.

Creating Functions Outside Migrations

Defining functions directly in production (not in version-controlled migrations) means environments drift and the logic isn't reproducible or reviewable. Always ship database code through your migration pipeline.

FAQ

What's the difference between a function and a stored procedure?

A function computes and returns a value (scalar, row, or set) and runs entirely within the transaction that called it — it can't control transactions itself. A stored procedure (called with CALL) can manage transactions internally, issuing COMMIT/ROLLBACK between statements, which suits multi-step batch operations. Use functions for reusable computations within a transaction; use procedures when you need internal transaction control.

When should I use a trigger?

For logic that must run automatically on data changes to guarantee integrity or bookkeeping — maintaining updated_at, writing audit/history records, keeping a denormalized or full-text column in sync, or enforcing an invariant no code path may bypass. Avoid triggers for core business logic: they fire invisibly, so complex trigger logic becomes hidden, hard-to-test behavior. Reserve them for data-protection and housekeeping.

Should business logic live in the database or the application?

Mostly the application, for business rules, workflows, and orchestration — that's where your tests, version control, debugging tools, and team visibility live. Use the database for what it's uniquely good at: guaranteeing data integrity, auditing, housekeeping, and data-heavy set operations. A useful split: the database protects data, the application makes decisions.

What are BEFORE, AFTER, and INSTEAD OF triggers?

They differ by when they fire. A BEFORE trigger runs before the change is applied and can validate or modify the row being written (or reject it) — ideal for setting timestamps or defaults. An AFTER trigger runs after the change and is used to react — audit logging, cascading updates, notifications. An INSTEAD OF trigger applies to views, translating a change on the view into changes on underlying tables to make the view writable.

Do functions run inside my transaction?

Yes — a function executes within the transaction that calls it, so its effects commit or roll back atomically with that transaction, and an unhandled error inside it rolls the whole transaction back. This is what makes in-database logic useful for atomic, all-or-nothing operations. Stored procedures are the exception: they can manage their own transactions internally.