Views, functions, procedures and triggers

Simple and materialised views, stored routines, triggers and their hidden cost, and how to decide which logic belongs in the database.

Views

A view stores a query, not rows. It gives a stable name to a shape your application depends on, and lets you change the tables underneath without changing every caller.

CREATE VIEW order_totals AS
SELECT o.id AS order_id,
       o.customer_id,
       SUM(i.quantity * i.unit_price) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.customer_id;

SELECT * FROM order_totals WHERE total > 500;

CREATE OR REPLACE VIEW order_totals AS ...;   -- change the definition, keep the name
DROP VIEW order_totals;

• A plain view runs its query every time it is read; nothing is stored and nothing is cached.
• Views are a compatibility layer: rename a column in a table, adjust the view, and old callers keep working.
• Most engines allow INSERT through a view only when it maps to a single table with no aggregation — otherwise you need an INSTEAD OF trigger.
• Layers of views hide cost: a view built on a view built on a view can run the same expensive join several times in one query.

Materialised views

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', placed_at) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX ON monthly_revenue (month);   -- required for CONCURRENTLY

REFRESH MATERIALIZED VIEW monthly_revenue;              -- blocks readers while it runs
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue; -- slower, does not block readers

A materialised view writes the result to disk. Reads become as fast as reading a table, and the data is stale until the next refresh — so the real question is how stale a number is allowed to be, not how fast the query is.

Functions, procedures and triggers

-- a function: returns a value and can be called inside a query
CREATE FUNCTION order_total(p_order_id BIGINT)
RETURNS NUMERIC
LANGUAGE sql STABLE AS $$
  SELECT COALESCE(SUM(quantity * unit_price), 0)
  FROM order_items WHERE order_id = p_order_id;
$$;

SELECT id, order_total(id) AS total FROM orders WHERE id = 42;

-- a procedure: called for its side effects and may manage transactions
CREATE PROCEDURE close_stale_orders(p_days INT)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE orders SET status = 'cancelled'
  WHERE status = 'open' AND placed_at < now() - make_interval(days => p_days);
END;
$$;

CALL close_stale_orders(30);

-- a trigger: runs automatically whenever matching rows change
CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

• A function extends the query language and is used in SELECT; a procedure is invoked with CALL and can commit or roll back.
• A row-level trigger runs once per changed row, so a bulk update of a million rows becomes a million extra calls.
• Triggers are invisible at the call site: a developer who inserts one row has no idea that three other tables changed. Document them or avoid them.
• Keep frequently changing business rules in the application and unbypassable invariants in the database — that split is what makes both layers testable.

FAQ

Related

Creating and altering schema with DDL Transactions and isolation levels

Last refreshed 2026-09-18.