Module 15: Views, functions and security

Package logic in the database: views, PL/pgSQL functions, triggers, roles and GRANT, and stopping SQL injection.

1. Views and materialised views: A view stores a query and is always fresh; a materialised view stores rows and is stale until REFRESH MATERIALIZED VIEW.
2. Functions and procedures in PL/pgSQL: Functions return values and are used in queries (SELECT f(x)); procedures do work and are run with CALL p(x).
3. Triggers: audit logs, updated_at and rules: A trigger runs a function on INSERT/UPDATE/DELETE: BEFORE to change or block the row (RETURN NEW), AFTER for side effects like audit logs.
4. Roles, GRANT/REVOKE and SQL injection: Grant each role only what it needs (GRANT/REVOKE, test with SET ROLE), and never glue user input into SQL: pass it as a parameter.

Ends with a 12-question checkpoint (80% passes) and a project: The order-status control room.