Chapter 15 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "CREATE VIEW," "CREATE MATERIALIZED VIEW," "REFRESH MATERIALIZED VIEW." Syntax, updatable-view rules, WITH CHECK OPTION, and CONCURRENTLY. https://www.postgresql.org/docs/current/sql-createview.html
  • PostgreSQL Docs: "CREATE FUNCTION," "CREATE PROCEDURE," "PL/pgSQL." The procedural language, parameters, volatility categories, and control flow. The PL/pgSQL chapter is excellent.
  • PostgreSQL Docs: "Triggers" and "CREATE TRIGGER." Trigger timing/events, NEW/OLD, row vs. statement triggers, and INSTEAD OF triggers for views.

Materialized views & caching (📊 Analyst · 🏗️ DBA)

  • pg_cron (or external schedulers) for scheduling REFRESH. The practical answer to Case Study 1.
  • Articles on "materialized view refresh strategies." Scheduled vs. event-driven vs. incremental refresh, and their trade-offs.
  • Incremental/“live” materialized views (extensions like pg_ivm, or tools like Materialize). For when periodic refresh isn't fresh enough.

Functions & procedural code (💻 Developer · 🔬 CS Student)

  • PL/pgSQL by example tutorials. Variables, loops, exceptions, RETURNS TABLE, and SECURITY DEFINER (relevant to Chapter 32).
  • Function volatility (IMMUTABLE/STABLE/VOLATILE). Why it matters for the optimizer and for expression indexes (Chapter 23).

Triggers — use with care (🏗️ DBA · 💻 Developer)

  • "When (not) to use triggers." Reinforces Case Study 2 — triggers for auditing/derived data, constraints for rules, explicit code for cross-system side-effects.
  • Audit-logging patterns with triggers. A canonical good use; many ready-made approaches exist.

Reference (this book)

  • Appendix C — SQL Quick Reference: view/function/trigger syntax.
  • Appendix I — SQL Cookbook: view and audit-trigger recipes.

Do, don't just read

  • Build a view, a matview (with scheduled refresh), a function, and an audit trigger on Mercado. Experiencing all four cements when to reach for each.
  • Reproduce the staleness trap: query a matview, change base data, query again (unchanged), then REFRESH and query once more.
  • Run \d orders and read the full output — including any triggers — so you never get ambushed by invisible logic.

Next: Chapter 16 — Advanced SQL: JSONB, full-text search, arrays, and PostgreSQL superpowers — the finale of Part II.