Week 9: Views, Stored Procedures, Functions & Triggers

1. CREATE VIEW

A view is a named, saved query — querying it looks and behaves exactly like querying a table, but it always reflects the underlying data live, computed fresh on every read.

saving Week 4's customer-totals query as a view

CREATE VIEW customer_totals AS
SELECT customers.id, customers.name, SUM(orders.total) AS total_spent
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

querying it like a table

SELECT * FROM customer_totals WHERE total_spent > 100;

A view is purely a convenience — it doesn't store data itself, just the query definition — which is exactly why it stays automatically up to date: every SELECT against it re-runs the underlying query against current data. (Postgres also supports MATERIALIZED VIEW, which does store a snapshot for performance, at the cost of needing an explicit refresh — a genuinely different tradeoff, worth knowing exists but out of scope here.)

2. Functions & PL/pgSQL Basics

a function computing a customer's total spend

CREATE FUNCTION get_customer_total(cust_id INT)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC;
BEGIN
    SELECT SUM(orders.total) INTO total
    FROM orders
    WHERE customer_id = cust_id;

    RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;

calling it

SELECT get_customer_total(1);

PL/pgSQL is Postgres's own procedural extension to SQL — real variables (DECLARE), control flow, and the ability to run multiple statements as one unit. It's genuinely a different kind of tool than a plain query; reach for a function when logic needs to branch, loop, or compose several queries together in a way a single SELECT can't express.

3. Triggers

a trigger keeping an updated_at column current automatically

CREATE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

After this, any update to orders — from any application, any script, any developer poking around in psql — automatically stamps updated_at, with no application code needing to remember to do it. This is a trigger's real strength: enforcing behavior that must hold no matter what wrote the data, which application-level code can never fully guarantee.

4. When to Push Logic Into the Database

Database-side logic is powerful and easy to overuse. A grounded framework:

Good fit — invariants that must hold regardless of what writes the data (an updated_at timestamp, a constraint from Week 6), and set-based operations genuinely faster done in the database than round-tripped to an application.
Poor fit — business logic that changes often, needs unit tests in your application's normal test suite, or that most of the team can't easily read (PL/pgSQL is a smaller, less familiar skill set than most application languages).
Triggers in particular are easy to lose track of — a bug in "why did this row change" can hide in a trigger nobody remembered exists, invisible from the application code someone's actually reading.

The practical default: keep the database in charge of correctness (constraints, simple triggers for genuine invariants); keep the application in charge of business logic that changes as the product does.

5. Hands-on Exercise

Hands-on

Add a view, a function and a trigger to the orders database

Apply all three database-side tools to the schema built across earlier weeks.

Requirements:

A view customer_order_summary showing each customer's name, order count, and total spend, using a join and GROUP BY.
A PL/pgSQL function get_top_customer() returning the name of the single customer with the highest total spend (handle the case of no orders existing at all gracefully).
An updated_at column added to orders, with a trigger that sets it automatically on every update, verified by updating a row and checking the timestamp changed with no application code setting it directly.
A trigger that prevents an order's total from ever being updated to a negative value, raising an error instead (as an alternative to a CHECK constraint, to see the trigger-based approach).
A short comment for each of the three: whether it was the right tool for the job, or whether a CHECK constraint / application-level code would have been simpler — applying this week's framework honestly, not just because the exercise asked for a trigger.

Hint

If a trigger function raises an unexpected error about NEW not being defined, check which event you're using it for — NEW (the about-to-be-written row) is only available on INSERT and UPDATE triggers, while a DELETE trigger only has OLD (the row being removed) available, since there's no new row on a delete.

6. Knowledge Check

Four quick questions. Expand each to check your answer.

Why does a view always reflect current data, with no risk of it going stale, while a materialized view can?

A plain view stores only the query definition, not any data — every SELECT against it re-executes the underlying query against the database's current state, the same as running that query directly. A materialized view stores an actual snapshot of the result at the time it was last refreshed, which means it can genuinely fall behind the real data until it's explicitly refreshed again.

What's the key capability PL/pgSQL adds that a plain SQL query can't do on its own?

Real procedural control flow — variables via DECLARE, conditional branching, loops, and composing multiple queries together as one logical unit with intermediate steps. A single SQL query can filter, join, and aggregate, but it can't express "do this, then based on the result do that" the way a PL/pgSQL function's imperative logic can.

Why is a trigger a stronger guarantee for something like an updated_at timestamp than remembering to set it in application code?

A trigger runs automatically for every write to the table regardless of what wrote it — the application, a script, a manual psql session, a future application nobody has written yet — where application-level code only sets the timestamp for writes that happen to go through that specific code path, and it's easy to forget in a new code path later.

Per this week's framework, why might a trigger be a poor fit for validating a business rule that's expected to change frequently, even though it would technically work?

PL/pgSQL is a smaller, less familiar skill set for most teams than their primary application language, and logic hidden in a trigger is easy to lose track of — a change to a frequently-evolving business rule buried in a trigger is harder to find, review, and test through the application's normal test suite than the equivalent logic living directly in application code where the rest of the team already looks.