Learn to encapsulate and reuse query logic in PostgreSQL using views, materialized views and functions. You will create and update views, cache expensive queries with materialized views and REFRESH, write parameterised SQL functions, and choose the right tool for each job so your database logic stays clean and reusable.
Watch the free preview
Creating and using views — free to watch, no account needed.
What you'll learn
- Encapsulate and reuse query logic with PostgreSQL views, materialized views and functions
- Create simple and updatable views and control writes with WITH CHECK OPTION
- Cache expensive queries using materialized views and REFRESH MATERIALIZED VIEW CONCURRENTLY
- Write SQL and parameterised functions that return scalars, rows and sets
- Decide when a view, a materialized view or a function is the right abstraction
Syllabus
Views — Naming and Reusing Queries
Creating and using viewsFree preview
Updatable views and WITH CHECK OPTION
Materialized Views — Caching Expensive Queries
Materialized views and REFRESH
Refresh strategies and CONCURRENTLY
Functions — Reusable, Parameterised Logic
SQL functions
Parameterised and procedural functions
Choosing the Right Abstraction
Views vs functions — when to use which
Composing views, functions and security
Lab — 10 Exercises & Solutions
Exercises 1–5
Exercises 6–10