197: Functions, Procedures, Triggers, Generated Logic, and When to Keep Logic in SQL

Learning outcomes

By the end of this lesson, you can:

explain and apply sql and pl/pgsql functions in a realistic implementation;
explain and apply procedures in a realistic implementation;
explain and apply triggers in a realistic implementation;
explain and apply generated columns in a realistic implementation;
explain and apply security definer in a realistic implementation.

These outcomes are about making design decisions, not just recalling five PostgreSQL features. You should be able to identify the contract each feature provides, understand what happens when that contract is violated, and justify keeping a piece of logic in the database or moving it to application code.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before reading, retrieve one concrete example from a previous project where the same concern appeared. Perhaps a service calculated a value repeatedly, an audit record had to be written whenever data changed, or a business rule was split between application code and the database. Use that example to test your design instincts as you read.

The goal is not to memorize terminology. It is to make a defensible decision inside a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. A feature that looks convenient in isolation can create hidden coupling once multiple services, retries, migrations, and concurrent requests depend on it.

Terminology

SQL and PL/pgSQL functions: Functions encapsulate reusable query or procedural behavior and can return scalar values, rows, or sets. SQL functions are often a good fit for data-local computation; PL/pgSQL adds procedural control flow when a function genuinely needs it.
Procedures: Procedures are invoked with CALL and support transaction-control scenarios that differ from ordinary functions. That difference is a boundary decision, not merely a naming preference.
Triggers: Triggers run automatically in response to data events. They are useful for auditing, maintaining derived columns, or enforcing cross-row behavior that ordinary constraints cannot express, but their automatic side effects can be easy to miss.
Generated columns: Stored generated columns derive values from expressions and keep those values synchronized automatically. They have restrictions on the expressions they may use, so they are intended for appropriate deterministic derivation rather than arbitrary workflow logic.
Security definer: Security-definer routines execute with the privileges of their owner. They require a carefully controlled search_path, input validation, and least-privilege design; otherwise, a routine intended to expose a narrow operation can become a privilege-escalation path.
Logic placement: Prefer constraints for declarative invariants, SQL functions for reusable data-local computation, and application services for workflows involving external systems or rapidly evolving business orchestration. The right answer depends on where the invariant can be enforced most clearly and reliably.

Mental model

Treat Functions, Procedures, Triggers, Generated Logic, and When to Keep Logic in SQL as a design problem with observable inputs, outputs, invariants, and failure modes. Database-side logic can enforce invariants close to the data and can reduce round trips, but hidden procedural behavior increases coupling. It still needs to be versioned, tested, reviewed, and observable like application code.

A strong implementation makes assumptions visible, narrows uncertainty at system boundaries, and leaves enough evidence—tests, types, constraints, metrics, or diagrams—to show why the design is safe. The database is not a magical place where correctness happens automatically, and application code is not automatically clearer simply because it is easier to open in an editor.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call or a familiar database feature. First state what must remain true. Then choose the mechanism that enforces it, identify who owns errors and recovery, and decide how you will observe the behavior when it fails.

Deep dive

1. SQL and PL/pgSQL functions

Functions encapsulate reusable query or procedural behavior and return scalar, row, or set results. A function can give callers a stable database-side contract, but that contract includes more than its parameter list: result shape, null behavior, error behavior, volatility, and security context all matter.

SQL functions are useful when the operation is naturally expressed as a query. PL/pgSQL is useful when the operation needs procedural control flow, local variables, branching, or carefully bounded exception handling. Volatility declarations and security context affect planner and security semantics, so they should describe the actual behavior rather than being selected casually.

Decision rule: Use sql and pl/pgsql functions deliberately when they make the contract or invariant easier to prove. If a function only reduces typing while hiding an assumption, prefer the more explicit design. Before adopting one, check how callers will test it, how its result will be observed in a plan or log, and how its definition will be deployed alongside schema changes.

2. Procedures

Procedures are invoked with CALL and support transaction-control scenarios different from ordinary functions. That distinction matters when the database-side operation must coordinate work at a transaction boundary that a normal function cannot control. A procedure is therefore not simply a function with a different keyword.

Use only when database-side orchestration is truly the right boundary. Consider who invokes the procedure, what transaction state the caller expects, what happens on partial failure, and how a migration or application retry will interact with it. If the workflow depends on an external service, a queue, or rapidly changing business policy, moving the orchestration into an application service may make ownership and recovery clearer.

Decision rule: Use procedures deliberately when they make the contract or invariant easier to prove. If a procedure only reduces typing while hiding an assumption, prefer the more explicit design.

3. Triggers

Triggers run automatically on data events and are useful for auditing, maintaining derived data, or enforcing cross-row behavior that cannot be expressed by ordinary constraints. They execute as part of the statement or transaction that caused the event, which can provide strong consistency for the behavior they own.

That automatic execution is also the main risk. A trigger can add writes, reject an otherwise valid-looking operation, or affect bulk loads without appearing in the statement issued by the application. Hidden side effects can surprise bulk loads and debugging, particularly when several triggers or tables are involved. Document the trigger, test the complete transaction behavior, and make its failures visible to callers.

Decision rule: Use triggers deliberately when they make the contract or invariant easier to prove. If a trigger only reduces typing while hiding an assumption, prefer the more explicit design.

4. Generated columns

Stored generated columns derive values from expressions and keep them synchronized automatically. They are preferable to triggers for simple deterministic derivation because the relationship is visible in the table definition and does not require a separate event handler to maintain the value.

They are not a general-purpose place for arbitrary business workflows. Restrictions on allowed expressions are part of the feature's safety model. Check whether the value depends only on data available in the row and whether its derivation remains valid as the schema evolves. If the value requires external state, side effects, or procedural coordination, another mechanism is more appropriate.

Decision rule: Use generated columns deliberately when they make the contract or invariant easier to prove. If a generated column only reduces typing while hiding an assumption, prefer the more explicit design.

5. Security definer

Security-definer routines execute with owner privileges. This can be useful when callers should be allowed to perform one narrowly defined operation without receiving broad direct access to the underlying tables. It also means that routine code, object lookup, and input handling must be treated as security-sensitive.

Use a carefully controlled search_path, validate inputs, and grant only the minimum privileges needed. Avoid treating a security-definer routine as a shortcut around authorization. The routine should define a narrow, reviewable capability, and its owner and dependencies should be managed deliberately. Otherwise, an attacker may influence name resolution or inputs and turn the elevated execution context into privilege escalation.

Decision rule: Use security definer deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design and verify that ordinary authorization cannot be bypassed.

6. Logic placement

Prefer constraints for declarative invariants, SQL functions for data-local reusable computation, and application services for workflows that involve external systems or rapidly evolving business orchestration. Triggers and procedures can be appropriate when transaction-local behavior or database-owned consistency is the requirement, while generated columns fit simple deterministic values derived from row data.

The useful distinction is ownership. A database constraint is visible to every writer. A database function provides a callable data operation. A trigger runs because a data event occurred, whether or not the caller remembered it. An application service can coordinate external work and changing policy, but it cannot replace a database guarantee when multiple writers must remain consistent.

Decision rule: Use logic placement deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Start by writing the requirement in one sentence, list the input and output contracts, and identify which of the concepts above owns each failure mode. The important move is separation: parsing or validation belongs at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing these concerns can make a happy-path demo look shorter, but it makes edge cases and ownership much harder to reason about.

For example, this query reads active customers and includes customers who have no orders:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN is significant: an active customer with no matching order still appears, and COUNT(o.id) returns zero for that customer because the joined order id is null. Grouping by both selected customer columns makes the aggregation explicit. Ordering by the alias communicates that the result is ranked by the computed count, though ties still need a deliberate policy if callers require stable ordering.

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate, retry, or concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. This is the level of explanation expected in a senior code review or technical interview. Also ask whether the query's plan and indexes support the expected data size; a correct result is not the same thing as acceptable production behavior.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence from an execution plan, metrics, logs, or a reproducible test.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and the network input is untrusted. A database routine can protect a boundary, but it does not remove the need to consider authorization, retries, deployment ordering, and operational visibility.

Guided lab

Implement an audit trigger and one data-local function, then rewrite one candidate trigger as an application workflow. Compare discoverability, transaction guarantees, testability, and deployment coupling. The point is not to prove that one location is always better. It is to make the trade-off concrete: which implementation is easier to find, which one is atomic with the write, which one is easier to test in isolation, and which one is easier to evolve safely?

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

When inspecting the result, include the behavior that is not visible in the initiating application statement. Check the audit row, the function's result and errors, the transaction outcome, and the relevant logs or query plan. If the application workflow and trigger differ under rollback or retry, record that difference rather than treating it as an implementation detail.

Edge cases and failure modes

SQL and PL/pgSQL functions: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify null handling and returned shape as well as the successful value.
Procedures: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Include transaction-boundary and partial-failure behavior.
Triggers: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Include bulk operations, rollback, and the trigger's hidden writes or rejection paths.
Generated columns: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify the generated value after inserts and updates and confirm that its expression restrictions match the intended design.
Security definer: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Test unauthorized callers, search-path behavior, input validation, and least-privilege boundaries.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case, inspect the actual value or execution plan, trace the boundary where the invariant first becomes false, and fix the owning layer rather than adding a downstream patch. Start by separating the possible boundaries: source or build, browser or DOM, Network or HTTP, server or route, database or query, and deployment or configuration. For database-side logic specifically, inspect the routine definition, trigger registrations, generated-column expression, privileges, transaction state, and relevant logs before assuming the initiating statement tells the whole story.

Interview questions

What problem does SQL and PL/pgSQL functions solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Procedures solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Triggers solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Generated columns solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Security definer solve, and what trade-off or failure mode would make you choose a different approach?

Answer these with a concrete invariant and failure mode rather than a feature definition alone. A strong answer explains what the mechanism guarantees, what it hides or complicates, and how you would verify the behavior in a real PostgreSQL application.

Checkpoint

Without notes, explain Functions, Procedures, Triggers, Generated Logic, and When to Keep Logic in SQL to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Be prepared to say which layer owns each guarantee and what evidence would convince you that the implementation remains correct under retries and concurrency.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.