Working with SQL Views
1. Creating Views
| Form | Example |
|---|---|
| Basic | CREATE VIEW v AS SELECT ...; |
| Replace | CREATE OR REPLACE VIEW v AS ...; |
| With check option | WITH CHECK OPTION — enforce predicates on writes |
| Security barrier (PG) | WITH (security_barrier=true) |
| Drop | DROP VIEW [IF EXISTS] v; |
2. Querying Views
| Aspect | Detail |
|---|---|
| Same as table | SELECT ... FROM view WHERE ... |
| Planner | Inlines view definition into outer query |
| Predicate pushdown | Usually works through views |
3. Creating Updatable Views
| Requirement | Detail |
|---|---|
| Simple SELECT | No JOIN, GROUP BY, DISTINCT, set ops |
| All NOT NULL cols selectable | For inserts to succeed |
| INSTEAD OF triggers | Make complex views writable manually |
| WITH CHECK OPTION | Blocks writes that violate view filter |
4. Using Views for Security
| Pattern | Use |
|---|---|
| Column hiding | Project only allowed columns |
| Row filtering | WHERE owner = current_user |
| Aggregation only | Expose summaries, hide individual rows |
| Grant on view only | Revoke base table access from app role |
Example: Per-User View
CREATE VIEW my_orders WITH (security_barrier=true) AS
SELECT id, total, placed_at FROM orders
WHERE customer_id = current_setting('app.customer_id')::BIGINT;
GRANT SELECT ON my_orders TO app_role;5. Creating Materialized Views
| Aspect | Detail |
|---|---|
| Persistent | Stored on disk as a table |
| Stale | Until refreshed |
| Indexable | Yes — create indexes for fast lookups |
| WITH DATA / NO DATA | Populate now or empty |
6. Refreshing Materialized Views
| Mode | Detail |
|---|---|
| REFRESH MATERIALIZED VIEW v | Exclusive lock, blocks readers |
| REFRESH ... CONCURRENTLY | Non-blocking, requires unique index |
| ON COMMIT (Oracle) | Auto-refresh after committing source change |
| Incremental refresh | Oracle FAST refresh; PG via pg_ivm / triggers |
7. Indexing Materialized Views
| Index | Purpose |
|---|---|
| Unique index | Required for CONCURRENTLY refresh |
| Lookup indexes | Same as tables (B-tree, GIN, etc.) |
| Rebuild | Indexes are maintained on refresh |
8. Dropping Views
| Form | Detail |
|---|---|
| DROP VIEW v | Errors on dependents |
| DROP VIEW v CASCADE | Drops dependent views/objects |
| DROP MATERIALIZED VIEW | Separate command |
9. Managing View Dependencies
| Catalog | Query |
|---|---|
| PG | pg_depend, information_schema.view_table_usage |
| MySQL | information_schema.views |
| Oracle | DBA_DEPENDENCIES |
| Rebuild strategy | Drop in reverse-dependency order, recreate forward |
10. Optimizing View Performance
| Tip | Detail |
|---|---|
| Simple views inline | Planner sees through them |
| Avoid view stacking | Multi-level views complicate planner |
| Materialize hot views | If freshness allows |
| Functional indexes | If view uses expressions |