Views, Functions & Extensions

Views: Named Queries

A view is a saved SELECT query that acts like a virtual table. It simplifies complex queries, enforces consistent access patterns, and lets you restrict which columns users can see without duplicating data.

-- Regular view: re-executes the query on every access
CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM   users
WHERE  deleted_at IS NULL;

-- Use the view exactly like a table
SELECT name FROM active_users WHERE created_at > NOW() - INTERVAL '30 days';

-- Views can join multiple tables
CREATE VIEW post_summary AS
SELECT p.id,
       p.title,
       p.slug,
       u.name  AS author,
       COUNT(c.id) AS comment_count
FROM   posts p
JOIN   users u ON u.id = p.user_id
LEFT   JOIN comments c ON c.post_id = p.id
WHERE  p.published = true
GROUP  BY p.id, p.title, p.slug, u.name;

Materialized Views

A materialized view stores the query result on disk. Reads are very fast because no computation happens at query time. The trade-off is staleness — you must refresh it periodically.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at)::DATE AS day,
       COUNT(*)                             AS order_count,
       SUM(total)                           AS revenue
FROM   orders
WHERE  status = 'completed'
GROUP  BY 1
ORDER  BY 1 DESC;

-- Refresh (replaces all data; can be run on a schedule or via trigger)
REFRESH MATERIALIZED VIEW daily_revenue;

-- Refresh without locking reads (requires a UNIQUE index on the view)
CREATE UNIQUE INDEX ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

PL/pgSQL Functions

Functions encapsulate reusable logic in the database. PL/pgSQL (the default Postgres procedural language) supports variables, loops, conditionals, and exception handling.

-- Simple SQL function
CREATE OR REPLACE FUNCTION user_post_count(p_user_id BIGINT)
RETURNS BIGINT AS $$
  SELECT COUNT(*) FROM posts
  WHERE user_id = p_user_id AND published = true;
$$ LANGUAGE SQL STABLE;

-- PL/pgSQL function with logic
CREATE OR REPLACE FUNCTION transfer_credits(
  from_user BIGINT,
  to_user   BIGINT,
  amount    NUMERIC
) RETURNS VOID AS $$
BEGIN
  IF amount <= 0 THEN
    RAISE EXCEPTION 'Amount must be positive, got %', amount;
  END IF;

  UPDATE wallets SET balance = balance - amount WHERE user_id = from_user;
  UPDATE wallets SET balance = balance + amount WHERE user_id = to_user;
END;
$$ LANGUAGE plpgsql;

Triggers

-- Auto-update updated_at on any change
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();