Views & Materialized Views

-- 1) Basic view
CREATE VIEW active_users AS
SELECT id, email, name, created_at
FROM users
WHERE deleted_at IS NULL AND status = 'active';

-- Query like a table
SELECT * FROM active_users WHERE created_at >= now() - interval '30 days';

-- The view is not stored; it's re-evaluated each query.
-- Indexes on underlying tables still apply.

-- 2) Drop / replace
DROP VIEW active_users;
CREATE OR REPLACE VIEW active_users AS
SELECT id, email, name, created_at
FROM users
WHERE deleted_at IS NULL;

-- 3) View with joins + computed columns
CREATE VIEW order_summary AS
SELECT
    o.id,
    o.user_id,
    u.email      AS user_email,
    o.total,
    o.status,
    COUNT(i.id) AS item_count,
    o.created_at
FROM   orders o
JOIN   users  u ON u.id = o.user_id
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.user_id, u.email;

-- 4) View with parameters via functions
CREATE FUNCTION recent_orders(days int)
RETURNS TABLE (id bigint, user_id bigint, total numeric, created_at timestamptz) AS $$
    SELECT id, user_id, total, created_at
    FROM orders
    WHERE created_at >= now() - (days || ' days')::interval
$$ LANGUAGE SQL STABLE;

SELECT * FROM recent_orders(7);

-- 5) Materialised view — physical table, refreshed on demand
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
    date_trunc('day', created_at)::date AS day,
    SUM(total)                           AS revenue,
    COUNT(*)                              AS order_count
FROM   orders
WHERE  status = 'paid'
GROUP BY day
ORDER BY day;

-- Read like a table; fast (results pre-computed)
SELECT * FROM daily_revenue WHERE day >= now() - interval '7 days';

-- Refresh manually (locks the view)
REFRESH MATERIALIZED VIEW daily_revenue;

-- Refresh CONCURRENTLY — doesn't block reads
-- Requires a UNIQUE index on the view
CREATE UNIQUE INDEX ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

-- Schedule via pg_cron or external scheduler
SELECT cron.schedule('refresh daily_revenue', '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue');

-- 6) Updatable views — Postgres can sometimes write through views
CREATE VIEW recent_users AS
SELECT id, email, name, created_at
FROM   users
WHERE  created_at >= now() - interval '30 days';

-- This works:
UPDATE recent_users SET name = 'New' WHERE id = 1;
-- Postgres rewrites it to UPDATE users SET name = 'New' WHERE id = 1 AND ...

-- Restrictions: simple, single-table, no GROUP BY, no DISTINCT, no aggregates

-- 7) INSTEAD OF triggers — manual write logic for complex views
CREATE VIEW user_full AS
SELECT u.id, u.email, p.bio, p.avatar
FROM   users u
LEFT JOIN profiles p ON p.user_id = u.id;

CREATE OR REPLACE FUNCTION user_full_insert() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users (email)        VALUES (NEW.email)        RETURNING id INTO NEW.id;
    INSERT INTO profiles (user_id, bio, avatar) VALUES (NEW.id, NEW.bio, NEW.avatar);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_full_insert
INSTEAD OF INSERT ON user_full
FOR EACH ROW EXECUTE FUNCTION user_full_insert();

-- Now INSERT INTO user_full (email, bio, avatar) VALUES (...);
-- inserts into BOTH users and profiles.

-- 8) Security via views — hide sensitive columns
REVOKE ALL ON users FROM analyst;

CREATE VIEW user_public AS
SELECT id, name, created_at
FROM   users;

GRANT SELECT ON user_public TO analyst;
-- Analyst can read names but not emails/passwords.

-- Row-level security (RLS) — even finer control
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_orders ON orders FOR SELECT USING (user_id = current_setting('app.user_id')::bigint);

-- 9) Recursive views — leverage WITH RECURSIVE
CREATE RECURSIVE VIEW org_chart(id, manager_id, name, depth) AS
    SELECT id, manager_id, name, 0 AS depth
    FROM   employees
    WHERE  manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id, e.name, o.depth + 1
    FROM   employees e
    JOIN   org_chart o ON e.manager_id = o.id;

SELECT * FROM org_chart ORDER BY depth, name;

-- 10) Drop with dependencies
DROP VIEW order_summary CASCADE;       -- also drops dependent views
DROP VIEW order_summary RESTRICT;       -- fails if dependents exist (default)

-- 11) List views
SELECT * FROM information_schema.views WHERE table_schema = 'public';
\dv          -- psql shortcut

-- For materialised views:
SELECT * FROM pg_matviews WHERE schemaname = 'public';
\dm

-- 12) View definition
SELECT view_definition FROM information_schema.views WHERE table_name = 'order_summary';
\d+ order_summary    -- psql

-- 13) Common patterns

-- a) API-friendly shape
CREATE VIEW api_users AS
SELECT
    id,
    email,
    name,
    created_at,
    (SELECT count(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- b) Backwards-compatibility — rename a column without breaking clients
ALTER TABLE orders RENAME COLUMN amt TO total;
CREATE OR REPLACE VIEW orders_compat AS
SELECT id, user_id, total AS amt, total, status, created_at
FROM orders;
-- Clients can use 'amt' (legacy) or 'total' (new).

-- c) Materialised dashboard view
CREATE MATERIALIZED VIEW dashboard_stats AS
SELECT
    (SELECT count(*) FROM users)         AS user_count,
    (SELECT count(*) FROM orders)         AS order_count,
    (SELECT sum(total) FROM orders
     WHERE status = 'paid'
       AND created_at >= now() - interval '30 days') AS revenue_30d;

-- Refresh every 15 min via pg_cron

-- 14) Performance considerations
--   • Views — re-execute per query; same cost as inline SQL
--   • Materialised views — fast reads, but stale until refreshed
--   • Index materialised views like tables for query performance
--   • Use REFRESH CONCURRENTLY in production (needs UNIQUE index)
--   • Views with complex joins still benefit from underlying-table indexes

-- 15) Anti-patterns
--   ❌ Many nested views — query planner has to unroll them all
--   ❌ Materialised views without an indexable refresh strategy
--   ❌ Replacing tables with views (use the table directly)
--   ❌ Using views to enforce business rules that triggers should handle

-- 16) When to use views vs alternatives
-- View                : query reuse, shape simplification, security
-- Materialised view   : expensive query cached; periodic refresh
-- CTE                 : ad-hoc reuse within ONE query (not persisted)
-- Function returning TABLE : parametrised view
-- Application-side cache    : when business logic complicates SQL

-- 17) Best practices
--   ✅ Document complex views with a comment
COMMENT ON VIEW order_summary IS 'Joins orders + users + counts items';

--   ✅ Use materialised views for expensive aggregations + dashboards
--   ✅ REFRESH CONCURRENTLY (requires UNIQUE index)
--   ✅ Schedule refresh via pg_cron or external scheduler
--   ✅ Use views for backwards-compatibility during column renames
--   ✅ Use views to hide sensitive columns + grant SELECT to specific roles
--   ✅ Test view performance with EXPLAIN ANALYZE — same as for tables
--   ✅ Don't over-nest views; planner has to unfold them

CREATE VIEW active_users AS
    SELECT * FROM users WHERE last_login > now() - interval '30 days';

CREATE MATERIALIZED VIEW posts_per_day AS
    SELECT date_trunc('day', created_at) AS d, count(*) AS n
    FROM posts GROUP BY 1;
REFRESH MATERIALIZED VIEW posts_per_day;