Skip to main content

Views, Materialized Views, and Stored Procedures

What This Concept Is​

Three server-side abstraction tools that all live inside the database.

  • View: a named query. CREATE VIEW v AS SELECT .... The view does not store data; every query against it re-runs the underlying SELECT. Views are query sugar; they hide joins, filters, and projections behind a name. Some engines allow updatable views when the query is simple enough to map row-changes back onto base tables.

  • Materialized view: a named query plus its last computed result. The result is stored like a table and is queried like a table. It goes stale when underlying data changes, and is refreshed explicitly (REFRESH MATERIALIZED VIEW v) or on schedule. Some engines support REFRESH CONCURRENTLY to avoid blocking readers.

  • Stored procedure / function: a named body of SQL plus procedural logic (PL/pgSQL, T-SQL, etc.), stored in the database and invoked by CALL or SELECT function(...). Can return scalars, rows, or result sets; can contain transactions, loops, exception handling, and updates.

Why It Matters Here (connection to primary)​

Concepts 10 and 11 taught you to protect and evolve your schema. This concept adds the tools you use to publish it: stable query surfaces for consumers and precomputation for hot reads.

  • views let you ship a clean API to application teams while freely refactoring the underlying tables
  • materialized views are the most structured way to denormalize (Concept 8): the redundancy is derived from a query, not a trigger, and can be rebuilt from scratch
  • stored procedures move critical integrity or auditing logic close to the data, useful when the same rule is enforced by several services

Concrete Example​

View:

CREATE VIEW active_subscription AS
SELECT s.customer_id, s.plan_code, p.monthly_cents, s.started_on
FROM subscription s
JOIN plan p ON p.code = s.plan_code
WHERE s.ended_on IS NULL;

SELECT * FROM active_subscription WHERE customer_id = 42;

If you later reshape subscription into subscription_event, you can keep active_subscription as the stable contract.

Materialized view (for an expensive dashboard):

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', placed_at)::date AS day,
       SUM(total_cents) / 100.0 AS revenue
FROM orders
GROUP BY 1;

-- Refresh nightly:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Queries on daily_revenue return instantly; the tradeoff is staleness bounded by the refresh cadence.

Stored function (PostgreSQL PL/pgSQL):

CREATE OR REPLACE FUNCTION charge_customer(p_customer_id BIGINT, p_cents INT)
RETURNS BIGINT AS $$
DECLARE new_order_id BIGINT;
BEGIN
    IF p_cents <= 0 THEN RAISE EXCEPTION 'amount must be positive'; END IF;
    INSERT INTO orders(customer_id, total_cents, currency)
    VALUES (p_customer_id, p_cents, 'USD')
    RETURNING id INTO new_order_id;
    RETURN new_order_id;
END;
$$ LANGUAGE plpgsql;

SELECT charge_customer(42, 1500);  -- returns new order id

Common Confusion / Misconception​

"A view speeds things up." No. A plain view is the same query rewritten; it has no storage. Performance comes only from materialized views, indexes, or rewriting the query.

"Materialized views stay current." Only as of the last refresh. If you want up-to-the-second correctness, use a plain view. If you want low read latency and can tolerate minutes of staleness, materialize it.

"Stored procedures are always the right place for business logic." Procedures tie logic to a specific database and are harder to test and version than application code. Use them for data-integrity rules that must survive any caller, for performance-critical sequences (batched inserts), and for clean encapsulation of audit-logging triggers. Keep general business logic in the application where it is testable.

How To Use It​

  • Use views to stabilize query interfaces across schema refactors and to simplify repeated joins.
  • Use materialized views when a specific aggregation is read many times per write and staleness is acceptable; schedule a refresh and monitor its runtime.
  • Use stored procedures / triggers to enforce invariants that cross multiple tables, to maintain denormalized counters (Concept 8), and for auditing. Document each one; they are invisible to developers reading application code.
  • Never put the only copy of a piece of business logic in a trigger. Triggers should enforce or observe, not compute business intent.

Check Yourself​

  1. When does an updatable view stop being updatable?
  2. What is the difference between REFRESH MATERIALIZED VIEW v and REFRESH MATERIALIZED VIEW CONCURRENTLY v?
  3. Name one situation where a stored procedure is the right answer and one where it is the wrong answer.

Mini Drill or Application​

Using your subscription schema from Concept 10:

  1. Write a view active_subscription as shown.
  2. Write a materialized view monthly_mrr returning one row per month with total monthly revenue, and a refresh plan.
  3. Write a stored function cancel_subscription(p_customer_id BIGINT) that sets ended_on = now() for the customer's active subscription and raises an exception if no active subscription exists.

Read This Only If Stuck​