Chapter 15: Views, Materialized Views, and Functions — Reusable SQL Objects

Where you are: Part II, Chapter 15 of 40. You can write complex queries and build tables. Now you'll package logic into reusable database objects — views, materialized views, functions, and triggers — so you (and your applications) don't repeat yourself.

Learning paths: 💻 📊 🔬 🏗️ — views and matviews are for everyone (📊 analysts especially); functions and triggers lean 💻/🏗️.

Naming and reusing your queries

You've written queries that you'd want to reuse — a six-table join for "order details," a per-customer revenue aggregation. Copy-pasting them everywhere is fragile: fix a bug in one copy and the others stay wrong. Database objects let you define logic once and reference it by name:

• Views — a saved query that behaves like a virtual table.
• Materialized views — a saved query whose results are stored (cached) for speed.
• Functions — reusable, parameterized logic that returns a value or a table.
• Triggers — functions that fire automatically on data changes.

Views: virtual tables

A view is a named query. Selecting from it runs the underlying query; the view stores no data of its own:

CREATE VIEW active_product_catalog AS
SELECT p.product_id, p.name, p.price, c.name AS category, s.name AS supplier
FROM products p
JOIN categories c ON c.category_id = p.category_id
LEFT JOIN suppliers s ON s.supplier_id = p.supplier_id
WHERE p.is_active = true;

Now anyone can SELECT * FROM active_product_catalog WHERE price < 100; without knowing the joins. Views give you three big wins:

1. Simplification — hide a complex join behind a simple name; everyone queries the view, not the tangle.
2. Security — expose only certain columns/rows. Grant access to a view that omits sensitive columns (salaries, costs) while the base table stays restricted (Chapter 32).
3. Abstraction — the view is a stable interface. You can refactor the underlying tables and, as long as the view's output stays the same, every consumer keeps working (data independence, Chapter 3, in action).

Because a view is just a stored query, it's always up to date — it reflects the current base-table data every time you query it. (It also has no performance magic: querying a view costs the same as running its query; PostgreSQL inlines it into your outer query and optimizes the whole thing.)

Updatable views and WITH CHECK OPTION

Simple views (selecting from one table, no aggregation/DISTINCT) are updatable — you can INSERT/UPDATE/DELETE through them, and PostgreSQL applies the change to the base table. Add WITH CHECK OPTION to prevent writes that would fall outside the view's WHERE:

CREATE VIEW gold_customers AS
SELECT * FROM customers WHERE loyalty_tier = 'gold'
WITH CHECK OPTION;     -- can't UPDATE a row's tier to 'silver' through this view

Complex views (joins, aggregates) aren't directly updatable, but you can make them writable with INSTEAD OF triggers (below) — usually you just write to the base tables.

Materialized views: cached results

A plain view re-runs its query every time. For an expensive query (a big aggregation over millions of rows) that's queried often but whose data changes infrequently, that's wasteful. A materialized view computes the query once and stores the results, so reads are instant:

CREATE MATERIALIZED VIEW category_sales AS
SELECT c.category_id, c.name AS category,
       COUNT(DISTINCT o.order_id)        AS num_orders,
       SUM(oi.quantity * oi.unit_price)  AS revenue
FROM categories c
JOIN products p     ON p.category_id = c.category_id
JOIN order_items oi ON oi.product_id = p.product_id
JOIN orders o       ON o.order_id    = oi.order_id
GROUP BY c.category_id, c.name;

Querying category_sales now reads stored rows — no joins, no aggregation, just a table scan of the small result. The trade-off is staleness: the stored results don't update automatically. You refresh them on a schedule or when the data changes:

REFRESH MATERIALIZED VIEW category_sales;                 -- recompute (locks reads briefly)
REFRESH MATERIALIZED VIEW CONCURRENTLY category_sales;    -- no read lock (needs a UNIQUE index)

When to use which. Use a view when you want a live, always-current abstraction and the underlying query is cheap enough. Use a materialized view when the query is expensive, the data changes slowly relative to how often it's read, and slightly stale results are acceptable (dashboards, reports, leaderboards). This is the database-level answer to the "catalog page that timed out" (Chapter 9): cache the aggregate.

Common mistake. Forgetting that a materialized view is stale until refreshed. If you build a dashboard on a matview and never refresh it, it silently shows old numbers. Schedule the refresh (a cron job, a trigger, or pg_cron) at a cadence that matches how fresh the data must be.

User-defined functions

PostgreSQL lets you define your own functions — reusable logic callable from queries. The simplest are pure-SQL functions:

CREATE FUNCTION order_total(p_order_id integer) RETURNS numeric AS $$
    SELECT COALESCE(SUM(quantity * unit_price * (1 - discount)), 0)
    FROM order_items
    WHERE order_id = p_order_id;
$$ LANGUAGE sql STABLE;

SELECT order_id, order_total(order_id) FROM orders WHERE order_id <= 3;

For logic with variables, loops, and conditionals, use PL/pgSQL (PostgreSQL's procedural language):

CREATE FUNCTION tier_for_spend(p_spend numeric) RETURNS text AS $$
BEGIN
    IF    p_spend >= 5000 THEN RETURN 'platinum';
    ELSIF p_spend >= 2000 THEN RETURN 'gold';
    ELSIF p_spend >= 500  THEN RETURN 'silver';
    ELSE  RETURN 'standard';
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Functions encapsulate business logic so it lives in one place, callable from any query or application. Mark them IMMUTABLE/STABLE/VOLATILE accurately — it tells the optimizer whether results can be cached (and affects whether they can be used in indexes, Chapter 23).

Dialect Difference. Every database has a procedural language, but they differ: PostgreSQL's PL/pgSQL vs. Oracle's PL/SQL vs. SQL Server's T-SQL vs. MySQL's stored-program syntax. The concepts (parameters, control flow, return types) transfer; the syntax doesn't (Appendix J).

Stored procedures

Procedures (PostgreSQL 11+) are like functions but are invoked with CALL and can manage transactions (commit/rollback inside themselves) — useful for multi-step administrative operations and batch jobs:

CREATE PROCEDURE archive_old_orders(cutoff date) AS $$
BEGIN
    INSERT INTO archived_orders SELECT * FROM orders WHERE order_date < cutoff;
    DELETE FROM orders WHERE order_date < cutoff;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

CALL archive_old_orders('2023-01-01');

The distinction: functions return values and are used in queries; procedures perform actions and can control transactions.

Triggers: logic that fires automatically

A trigger runs a function automatically when data changes (INSERT/UPDATE/DELETE), BEFORE or AFTER the change. The classic use is an audit trail — automatically recording who changed what:

-- A trigger function (returns the special "trigger" type)
CREATE FUNCTION log_price_change() RETURNS trigger AS $$
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, changed_at)
        VALUES (OLD.product_id, OLD.price, NEW.price, now());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_price_audit
AFTER UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION log_price_change();

Now any price update — from any app, script, or person — is logged automatically. Inside the trigger, NEW is the new row and OLD is the previous one. Common uses: audit logs, maintaining derived/denormalized columns, enforcing complex cross-row rules a CHECK can't, and (INSTEAD OF) making complex views writable.

Common mistake — triggers as hidden logic. Triggers run invisibly. A developer who doesn't know a trigger exists will be baffled when an UPDATE mysteriously also writes to another table, or when performance drops because every insert fires expensive logic. Triggers are powerful but surprising — document them, keep them simple and fast, and prefer constraints (CHECK, FK) for rules that constraints can express. Reserve triggers for things constraints can't do (auditing, cross-table derived state).

Views as a stable interface

The most strategically important thing about views isn't simplification or security — it's abstraction, and it deserves a deeper look because it's a genuine architectural tool. A view is a stable interface between your tables and everything that consumes them. Applications, reports, and analysts query the view; the view queries the tables. As long as the view's output columns stay the same, you are free to restructure the tables underneath — split one table into two, rename columns, denormalize for performance, change types — and every consumer keeps working, because they only ever knew the view, never the tables.

This is data independence (Chapter 3) made operational. Consider a customer_summary view that joins customers to their address and computes their total spend. A hundred reports query that view. Six months later you decide to split the address into its own table for normalization, or to precompute spend into a maintained column for speed. Without the view, you'd have to find and update all hundred reports. With the view, you change only the view's definition to produce the same columns from the new structure, and the hundred reports never notice. The view absorbed the change. This is exactly how large systems evolve their schemas without breaking the world — the view layer is a shock absorber between the physical tables and their logical consumers.

The discipline this enables is to expose views, not tables, as your public interface, especially across team or system boundaries. Internal code can touch tables directly; external consumers, reports, and other teams get views. Then the tables are yours to refactor freely, and the views are the contract you promise to keep stable. Combined with the security benefit (a view can expose a safe subset of columns and rows while the base table stays locked down — Chapter 32), views become a layer of indirection that pays for itself many times over as a system grows. A view is cheap to create and costs nothing at query time (it's inlined and optimized with your query), so the only reason not to interpose one at an important boundary is forgetting the option exists. Remembering it is a mark of architectural maturity.

Materialized views: refresh strategy is the whole game

A materialized view trades freshness for speed — it stores the results of an expensive query so reads are instant — and the entire art of using one well comes down to the refresh strategy, because a matview is only as good as it is current. The headline trade-off is staleness: a matview shows the data as of its last refresh, so the question that decides everything is "how stale can this be, and how do I refresh it accordingly?"

There are several refresh strategies, suited to different freshness needs. Scheduled refresh — a cron job or pg_cron task running REFRESH MATERIALIZED VIEW every hour, every night, every five minutes — fits dashboards and reports where data that's minutes-to-hours old is perfectly acceptable. On-demand refresh — refreshing right after a known bulk data load — fits matviews that summarize data which changes only in discrete batches. Trigger-based refresh is possible but usually unwise for anything but tiny matviews, because refreshing on every underlying change defeats the purpose (you'd pay the expensive computation on every write). The crucial operational detail is REFRESH MATERIALIZED VIEW CONCURRENTLY, which refreshes without locking out readers — essential for a matview backing a live dashboard, since a plain REFRESH blocks reads while it runs. CONCURRENTLY requires the matview to have a unique index, a small setup cost that's almost always worth paying.

The classic failure mode, worth stating starkly, is the forgotten refresh: someone builds a beautiful matview-backed dashboard, ships it, and never schedules a refresh — so it silently shows the same stale numbers forever, and weeks later someone notices the "live" revenue figure hasn't moved. A materialized view without a refresh strategy is not a feature; it's a latent bug. So the rule is: never create a materialized view without, in the same breath, deciding and implementing how it gets refreshed. The matview and its refresh schedule are a single design unit. When you reach for one (the right call for expensive aggregates over slowly-changing data — the "dashboard that timed out" cure), specify its freshness requirement first, then choose the refresh cadence that meets it, then set up CONCURRENTLY if reads must not block. Get the refresh strategy right and a matview is one of the highest-leverage performance tools in the database; ignore it and the matview lies to everyone who trusts it.

Functions, procedures, and where logic should live

User-defined functions and procedures let you put logic — not just structure and data — inside the database, and this raises a genuine architectural question that every team wrestles with: how much business logic belongs in the database versus in the application? There's no universal answer, but understanding the trade-offs lets you decide well rather than by default.

The case for database logic is compelling for certain kinds of work. Logic that is fundamentally about data integrity — derived values that must always be consistent, cross-row rules a CHECK can't express, audit trails — belongs close to the data, because the database is the one place every write passes through (the same argument as for constraints). A function encapsulating a calculation used by many queries (order_total, tier_for_spend) lives best in one place, callable everywhere, rather than reimplemented in each application. And set-oriented bulk operations are dramatically faster done in the database than by shipping rows to application code and back. The volatility classification matters here: marking a function IMMUTABLE (same inputs always give same output), STABLE (consistent within a query), or VOLATILE (can change anytime) tells the optimizer how aggressively it can cache and reorder calls — and, for IMMUTABLE functions, even use them in indexes (Chapter 23).

The case against over-stuffing the database is equally real. Business logic that changes frequently, that's specific to one application, or that benefits from the application language's ecosystem (libraries, testing tools, version control workflows) often lives better in application code, where it's easier to test, deploy, and reason about. Logic buried in database functions and triggers can become invisible — a developer reading the application code has no idea that an UPDATE also fires a trigger that rewrites another table, leading to baffling behavior and hard debugging. Procedures (invoked with CALL, able to manage their own transactions) are powerful for batch and administrative jobs but can concentrate a lot of consequential logic out of sight.

The mature position is a balance: put integrity-critical and data-proximate logic in the database (constraints, simple functions, audit triggers); keep application-specific, frequently-changing business logic in the application; and document anything that lives in the database, especially triggers, so it's never invisible. The anti-pattern at both extremes — an application that treats the database as dumb storage and re-validates everything itself, or a database so full of triggers and procedures that the application is a thin shell over inscrutable database magic — tends to cause more pain than a thoughtful split. Knowing that functions, procedures, and triggers exist and what they're good at lets you make that split deliberately, which is the goal of this chapter: not to push all logic into the database, but to know when doing so is the right call.

A performance note

• Views have no inherent cost — they're inlined and optimized with your query. A slow view means a slow underlying query (fix the query/indexes, Chapters 23–24).
• Materialized views make reads fast at the cost of staleness and refresh time — the right trade for expensive, slowly-changing aggregates.
• Triggers add work to every affected row's write — a per-row trigger doing heavy work can dominate insert/update cost. Keep them lean.

Triggers in depth

Triggers are the most powerful and most dangerous of the reusable objects, so they reward careful understanding. A trigger binds a function to a table event, firing it automatically whenever that event occurs — and the variations in when and how it fires give you precise control. The timing is BEFORE, AFTER, or INSTEAD OF the event. A BEFORE trigger runs before the change is applied and can modify the row about to be written (or cancel it) — used for normalizing data on the way in (lowercasing an email, computing a derived column) or enforcing complex validation. An AFTER trigger runs once the change is committed to the row and is used for reacting to it — writing an audit record, updating a summary elsewhere, notifying another system. An INSTEAD OF trigger, defined on a view, replaces the operation entirely, which is how you make an otherwise-unupdatable complex view writable.

The granularity is FOR EACH ROW versus FOR EACH STATEMENT. A row-level trigger fires once per affected row — so a single UPDATE touching a thousand rows fires it a thousand times, with OLD and NEW giving access to each row's before-and-after values. A statement-level trigger fires once per statement regardless of how many rows it touched, useful for coarse logging ("someone modified this table") or for batch reactions. The row-level form is far more common because most trigger logic is about individual rows, but the per-row firing is also where triggers get expensive: heavy logic in a row-level trigger multiplies across every affected row, so a bulk update of a million rows runs the trigger a million times. Keeping row-level triggers lean is essential to keeping writes fast.

The canonical, genuinely excellent use of triggers is the audit trail — automatically recording every change to a sensitive table, capturing what changed, when, and (with a bit more setup) by whom. Because the trigger fires on every write through any path — application, admin script, manual psql — it guarantees a complete audit record in a way application-level logging never can; there's no door it doesn't guard. Other strong uses: maintaining a denormalized or derived column that must stay consistent (a cached count, a search vector), and enforcing cross-row or cross-table rules that a CHECK constraint structurally cannot express (a CHECK can only see one row). For these jobs, triggers are the right and sometimes the only tool.

But triggers carry a real hazard that the chapter's earlier warning bears repeating and expanding: they are invisible logic. A developer reading the application code sees an ordinary UPDATE products SET price = ... and has no way to know that this update silently fires a trigger writing to price_history, updating a materialized summary, and notifying a queue. When that hidden cascade causes a performance problem or a baffling side effect, it can take hours to discover a trigger is responsible, because nothing in the obvious code mentions it. The disciplines that keep triggers an asset rather than a trap: document them prominently (in the schema, in the team's knowledge base), keep them simple and fast, prefer constraints for anything a constraint can express (a CHECK is visible in the table definition; a trigger enforcing the same rule is hidden), and reserve triggers for the jobs only they can do — auditing, cross-table derived state, and making views writable. Used with that discipline, triggers are powerful; used casually, they become the source of a system's most mystifying bugs.

A worked scenario: a complete audit trail

Let's build the audit-trail pattern fully, since it's the trigger's best use and a genuinely common requirement. Suppose Mercado must keep a complete history of every change to product prices — a regulatory and business need, since pricing disputes and analyses require knowing exactly what a price was at any past moment. The goal: any change to a product's price, from any source, is automatically recorded, with no reliance on application code remembering to log it.

First, a history table to hold the record — what changed, from what to what, and when:

CREATE TABLE price_history (
    history_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id  integer NOT NULL REFERENCES products (product_id),
    old_price   numeric(10,2),
    new_price   numeric(10,2) NOT NULL,
    changed_at  timestamptz NOT NULL DEFAULT now()
);

Then a trigger function that records a row whenever a price actually changes, and a trigger binding it to products:

CREATE FUNCTION log_price_change() RETURNS trigger AS $$
BEGIN
    IF NEW.price IS DISTINCT FROM OLD.price THEN          -- only log real changes
        INSERT INTO price_history (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;                                            -- AFTER trigger: return value ignored but required
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_price_audit
AFTER UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION log_price_change();

Now study what this guarantees. Any price update — whether from the pricing application, an admin correcting a typo in pgAdmin, a bulk repricing script, or a developer's manual UPDATE in psql — automatically writes a history row, because the trigger fires on the write itself, not on any particular code path. The IS DISTINCT FROM comparison (the NULL-safe inequality from Chapter 3) ensures we only log actual changes, not updates that happen to set the price to its current value. The AFTER UPDATE FOR EACH ROW timing means it runs once per changed product, after the change is applied, with OLD and NEW exposing the before and after. This is the audit pattern's whole appeal: completeness by construction. No code path can forget to log, because the logging isn't in any code path — it's in the database, watching every write.

This same skeleton — a history table plus an AFTER row trigger comparing OLD and NEW — generalizes to auditing any sensitive table: customer data changes, order status transitions, permission grants. It's one of the patterns you'll implement again and again in real systems, and Chapter 21 (data modeling patterns) returns to audit trails as a first-class design concern. Build it once here and you'll recognize it everywhere. Just remember the discipline: document that this trigger exists, so the next developer who sees prices being logged isn't mystified about where it's happening.

The bigger picture: the database as a platform

This chapter marks a quiet but significant shift in how you should see a database. For most of Part II, the database was a place to store and query data — you asked it questions, it returned rows. With views, materialized views, functions, and triggers, the database becomes something more: a platform that can hold logic, enforce behavior, present interfaces, and react to events. It is no longer just a filing cabinet; it's an active participant in your system's architecture.

This matters because it expands what you can build well. A view turns a tangle of joins into a clean, shareable interface. A materialized view turns an expensive report into an instant one. A function turns a scattered calculation into a single source of truth. A trigger turns an easily-forgotten cross-cutting concern into an automatic guarantee. Each is a way of pushing logic into the database, close to the data, where appropriate — and the judgment of when it's appropriate is exactly the architectural skill this chapter has tried to build. The answer is rarely "all of it" or "none of it," but a deliberate split: integrity-critical and data-proximate logic in the database, application-specific and fast-changing logic in the application, with the database's reusable objects keeping the former coherent and DRY.

The PostgreSQL-specific dimension of this is theme #4, which the next chapter makes explicit: as the database absorbs more capability — not just these objects, but JSONB, full-text search, and the rest of Chapter 16 — it increasingly does jobs that teams once spun up separate systems to handle. A capable database with well-designed views, functions, and triggers can be the coherent center of an application in a way a dumb data store cannot. Seeing the database as a platform rather than mere storage is the mindset that lets you take full advantage of that capability — and it's the mindset Part II has been building toward all along. You began by asking simple questions of stored data; you end able to design data structures, encode rules as guarantees, compose sophisticated queries, and package logic into reusable objects. That is genuine SQL mastery, and this chapter is its consolidation.

One chapter remains in Part II, and it pushes the platform idea to its conclusion. Chapter 16 covers PostgreSQL's advanced features — JSONB for document storage, full-text search, arrays, and more — the capabilities that let a single PostgreSQL database do jobs people often assume require MongoDB, Elasticsearch, or a specialized store. Having seen here how views, functions, and triggers turn the database into a logic platform, you're ready to see how its data capabilities stretch just as far. Together, the reusable objects of this chapter and the advanced types of the next are the strongest evidence for theme #4: that PostgreSQL's full power genuinely does eliminate the need for a second database far more often than most teams realize. The mastery you've built across Part II is what lets you wield that power deliberately. A team that knows views, functions, triggers, JSONB, and full-text search can build remarkably capable systems on a single, well-understood database — avoiding the sprawl of specialized stores that each demand their own operations, backups, and expertise. Knowing what your one database can do is, increasingly, an architectural advantage in itself — and the next chapter shows just how much that one database can do.

Progressive project: package your logic

In your domain:

1. Create a view that hides a useful multi-table join (e.g., "loan details with member and book," "appointment with patient and provider"). Query it simply.
2. Create a materialized view for an expensive summary you'd show on a dashboard (e.g., "loans per month," "revenue per category"). Refresh it and note the staleness trade-off.
3. Write a function encapsulating a business rule (e.g., is_overdue(loan_id), late_fee(days)).
4. (Optional) Add an audit trigger that logs changes to a sensitive table.

Add these to project-notes.md.

Table-returning functions and function design

Functions aren't limited to returning a single value — they can return whole tables, which makes them parameterized, reusable queries: a view that takes arguments. This is one of the more powerful and underused features in PostgreSQL. A set-returning function declares RETURNS TABLE(...) (or RETURNS SETOF sometype) and is queried in the FROM clause like a table:

CREATE FUNCTION orders_for_customer(p_customer_id integer)
RETURNS TABLE(order_id integer, order_date timestamptz, total numeric) AS $$
    SELECT o.order_id, o.order_date,
           COALESCE(SUM(oi.quantity * oi.unit_price), 0)
    FROM orders o
    LEFT JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.customer_id = p_customer_id
    GROUP BY o.order_id, o.order_date;
$$ LANGUAGE sql STABLE;

-- Use it like a table, passing the parameter:
SELECT * FROM orders_for_customer(7) WHERE total > 100;

Where a view is a fixed query, a table-returning function is a query parameterized by its arguments — exactly the right tool when you want the convenience of a named, reusable query but need to vary it by input. You can join its output, filter it, and aggregate over it just like any table. This bridges the gap between views (reusable but fixed) and writing the full query every time (flexible but repetitive). For logic that's "the same query shape with a different parameter each time," a table-returning function is often the cleanest expression.

Good function design follows a few principles worth absorbing. Give functions clear names and parameter names (the p_ prefix convention distinguishes parameters from columns, avoiding ambiguity). Classify volatility accurately — IMMUTABLE for pure computations, STABLE for functions that read the database but don't change within a query, VOLATILE (the default) for anything with side effects or non-deterministic results — because this classification directly affects how the optimizer can cache and reorder calls. Keep functions focused on one job, as you would any well-factored code. And remember that a SQL-language function (LANGUAGE sql) is preferable to PL/pgSQL when the logic is a single query, because it's simpler and the optimizer can often inline it; reach for PL/pgSQL only when you genuinely need variables, loops, or conditionals.

Volatility, performance, and the cost of objects

Each reusable object carries a performance profile worth understanding, because the convenience they offer can hide real costs. Views are essentially free at definition time and, at query time, are inlined into your outer query and optimized as a whole — so a view costs exactly what its underlying query costs, no more and no less. A slow view is simply a slow query wearing a name; the fix is to optimize the underlying query and its indexes (Chapters 23–24), not to blame the view. This also means views don't accelerate anything — wrapping a slow query in a view doesn't make it faster, a common misconception.

Materialized views invert the trade: they make reads fast by storing results, at the cost of staleness and the periodic expense of refreshing. The refresh itself is a full re-execution of the query, so a matview over an expensive aggregate pays that expense on each refresh — which is why the refresh cadence must match how fresh the data needs to be, no fresher (refreshing a daily-needed dashboard every minute wastes enormous work). Functions mostly cost what their bodies cost, but the volatility classification matters for optimization: an IMMUTABLE function can be evaluated once and cached, even used in an index, while a VOLATILE one must be called every time. Mis-marking a function (claiming IMMUTABLE when it actually reads changing data) can produce wrong results, so the classification is a correctness concern, not just performance.

Triggers carry the most easily-overlooked cost: they add work to every write that fires them, and row-level triggers multiply that work across every affected row. A trigger doing something expensive turns a fast bulk update into a slow one, invisibly. This is why the chapter has stressed keeping triggers lean — a trigger is in the hot path of every write to its table, so heavy trigger logic is heavy write logic. The general lesson across all four objects: reusable objects trade some control for convenience, and knowing where each spends its time — views at read, matviews at refresh, triggers at write — lets you reach for the right one and avoid the performance surprises that come from using them without understanding their cost model.

DRY in the database: the unifying idea

Step back and you'll see that all four objects in this chapter serve one principle: Don't Repeat Yourself, applied to data logic. The same six-table join copy-pasted into twenty reports is twenty places to fix when it changes, twenty chances for them to drift apart; defined once as a view, it's one. The same business calculation reimplemented in the web app, the mobile backend, and the nightly report is three subtly-different versions of one rule; defined once as a function, it's one, callable everywhere. The audit logging that every code path must remember to do, and inevitably some forgets; defined once as a trigger, it's automatic and complete. In each case, the object is the single source of truth for a piece of logic, and everything else references it rather than duplicating it.

This is why these objects matter beyond their individual features. A database without them tends to accumulate the same logic scattered across application code, reports, and scripts — each copy a maintenance burden and a latent inconsistency. A database that uses them well concentrates each piece of logic in one authoritative place, close to the data it concerns, where it can be maintained, secured, and reasoned about as a unit. Views centralize query logic; functions centralize computational logic; triggers centralize reactive logic; materialized views centralize and cache. Together they let the database be not just a store of data but a coherent home for the logic that operates on that data.

The judgment, as the chapter has stressed, is which logic belongs here versus in the application — and the answer is "the data-proximate, integrity-critical, widely-shared logic," with application-specific and fast-changing logic staying in the application. But within that boundary, the DRY principle is the guide: if you find yourself writing the same query, the same calculation, or the same reactive behavior in more than one place, one of these objects probably wants to own it. Recognizing those repetitions and lifting them into the right object is a skill that keeps a growing system coherent rather than letting it fragment into a dozen drifting copies of the same idea. That coherence — one place for each piece of logic — is the lasting payoff of this chapter, and a hallmark of well-built database-backed systems.

A field guide to pitfalls, and a note on portability

A handful of mistakes recur with reusable objects, and recognizing them keeps these powerful tools from becoming liabilities. The forgotten matview refresh: a materialized view that's never refreshed silently serves stale data forever — never create one without implementing its refresh strategy in the same breath. Invisible trigger logic: an undocumented trigger causes baffling side effects and mysterious slowdowns; document triggers prominently and keep them lean. The slow-view illusion: blaming a view for being slow when the underlying query is the real culprit — a view has no performance magic, so fix the query and its indexes. Mis-marked function volatility: claiming IMMUTABLE for a function that reads changing data can produce wrong, cached results — classify accurately. Over-stuffing the database with logic: burying so much business logic in functions and triggers that the application becomes an inscrutable shell — keep application-specific, fast-changing logic in the application. Using a trigger where a constraint would do: a CHECK is visible in the table definition and simpler; reserve triggers for what constraints can't express. Run any misbehaving database object past this list and you'll usually find the cause quickly.

On portability: the concepts here are universal — every serious database has views, most have materialized views (or an equivalent), all have stored functions/procedures, and all have triggers — but the specifics vary considerably. Views are the most portable (standard SQL, consistent behavior). Materialized views exist in PostgreSQL and Oracle natively, while other databases emulate them with tables plus scheduled jobs. Functions and procedures diverge the most: PostgreSQL's PL/pgSQL, Oracle's PL/SQL, SQL Server's T-SQL, and MySQL's stored-program syntax are all different languages expressing the same ideas (parameters, control flow, return types). Trigger syntax and capabilities differ too, though the BEFORE/AFTER, row/statement model is broadly shared. Appendix J records the translations. As always, the thinking transfers even where the syntax doesn't: the instinct to centralize query logic in views, cache expensive aggregates in materialized views, encapsulate computation in functions, and automate cross-cutting reactions in triggers serves you in any database, even as the exact spelling changes. These objects are how you make a database a coherent home for logic, not just data — a goal every relational database shares.

Summary

Database objects let you define logic once and reuse it. Views are named queries (virtual tables) that simplify, secure, and abstract — always current, no stored data, no performance magic; simple ones are updatable (WITH CHECK OPTION guards writes). Materialized views store results for fast reads on expensive, slowly-changing queries, at the cost of staleness (you must REFRESH, optionally CONCURRENTLY). Functions (SQL or PL/pgSQL) encapsulate reusable logic that returns values; procedures (CALL) perform actions and can manage transactions. Triggers run functions automatically on data changes (audit trails, derived columns) — powerful but invisible, so document them and prefer constraints when they suffice.

You can now: - Create and use views to simplify, secure, and abstract queries. - Choose between a view and a materialized view, and refresh matviews. - Write SQL and PL/pgSQL functions, and call procedures. - Write triggers for auditing and derived data — and explain why to use them sparingly. - Reason about the performance trade-offs of each object.

What's next. Chapter 16 — Advanced SQL — PostgreSQL's superpowers: JSONB (document storage inside a relational database), full-text search, arrays, and more — the features that, as the book's theme #4 says, often eliminate the need for a second database. It's the finale of Part II's SQL mastery.

Practice in exercises.md, test yourself with the quiz, apply it in the case studies, review the key takeaways, and go deeper with further reading.