Intermediate20 min read

Views & Functions

Create views, materialized views, functions, and triggers in PostgreSQL.

Views

Views encapsulate queries behind a virtual table interface. Use them for security (column masking), simplifying reporting queries, and stable API over evolving schema.

Simple views are inlined into queries; complex views may materialize as subqueries—check EXPLAIN.

CREATE VIEW active_users AS
SELECT id, email, name FROM users WHERE deleted_at IS NULL;

Materialized Views

Materialized views store query results physically for fast reads. REFRESH MATERIALIZED VIEW updates data; CONCURRENTLY option requires unique index and avoids read locks.

Ideal for expensive aggregates refreshed on schedule rather than every query.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day, SUM(total) AS revenue
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Functions

PL/pgSQL functions implement server-side logic with variables, loops, and exception handling. SQL functions inline easily for simple expressions. Mark functions IMMUTABLE/STABLE/VOLATILE correctly for optimizer.

RETURNS TABLE and SETOF simplify returning multiple rows.

CREATE OR REPLACE FUNCTION user_order_count(p_user_id BIGINT)
RETURNS BIGINT AS $$
  SELECT COUNT(*) FROM orders WHERE user_id = p_user_id;
$$ LANGUAGE SQL STABLE;

Triggers

Triggers fire BEFORE or AFTER row/statement events. Use for audit trails, derived column maintenance, or enforcing rules hard to express as constraints.

Trigger functions return NEW/OLD row for BEFORE row triggers. Document side effects—hidden trigger logic confuses debugging.

Prefer generated columns over triggers for simple derived fields when possible
Statement-level triggers fire once per statement, not per row
Use pg_trigger catalog to audit existing triggers

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Choosing SQL vs Application Logic

Keep constraints and data-local rules in database. Keep business workflows integrating external systems in application tier.

Version functions and views in migrations. Test with pgTAP or integration tests in CI.