Plain Views: Logical Reuse
Create views to encapsulate complex queries, simplify reports, and give read access to a subset of columns.
Plain Views: Logical Reuse is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is a View?
A view is a saved SELECT. Every time you query the view, the underlying SELECT runs. Like a named query you can JOIN, filter, and aggregate over.
CREATE VIEW active_users AS
SELECT id, email, full_name
FROM users
WHERE deleted_at IS NULL;Using a View
Query it like any table:
SELECT COUNT(*) FROM active_users;
SELECT * FROM active_users WHERE email LIKE '%@gmail.com';Views Are Not Stored
The view definition is stored, not the result. Each query expands the view and runs the underlying SELECT. Same query plan considerations.
Why Views?
- Reuse — define a complex query once
- Abstraction — hide implementation (join 3 tables → "active_users")
- Security — grant SELECT on view without exposing the base tables
- Compatibility — keep old query shapes working after schema changes
CREATE OR REPLACE
Update a view in place — but only if column types and order match:
CREATE OR REPLACE VIEW active_users AS
SELECT id, email, full_name, last_login
FROM users
WHERE deleted_at IS NULL;
-- ERROR if columns conflict; DROP VIEW + CREATE VIEW instead.Views with JOINs and Aggregates
Common case: roll-up per user:
CREATE VIEW user_stats AS
SELECT u.id, u.email,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;Updatable Views
Simple views (single table, no aggregate, no DISTINCT) are updatable — you can INSERT/UPDATE/DELETE through them. PostgreSQL auto-updates them where possible.
Schema-Qualified Views
Put utility views in a dedicated schema:
CREATE SCHEMA api;
CREATE VIEW api.users_public AS
SELECT id, email, full_name FROM users WHERE deleted_at IS NULL;
GRANT SELECT ON api.users_public TO app_reader;Views and Permissions
By default a view runs with the privileges of the querying user — they still need permission on base tables. To let a low-privilege user query a sensitive table, use SECURITY DEFINER functions instead.
Performance Considerations
The planner expands views and applies its usual optimisations. Sometimes a view's GROUP BY blocks predicate push-down — break it up or rewrite if EXPLAIN shows a problem.
View Dependencies
Dropping a base table is blocked if a view depends on it. Use DROP TABLE ... CASCADE only when you mean it. List dependents:
SELECT * FROM pg_depend
WHERE refobjid = 'users'::REGCLASS;Recap
Views are reusable SELECTs.
- Define once, query many
- Abstraction + security
- Updatable when simple
- Re-executed on every query — no built-in caching
Quick Check
Does querying a view store its result?
Frequently asked questions
Is the “Plain Views: Logical Reuse” lesson free?
Yes — the full text of “Plain Views: Logical Reuse” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Plain Views: Logical Reuse”?
Create views to encapsulate complex queries, simplify reports, and give read access to a subset of columns. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Plain Views: Logical Reuse” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Plain Views: Logical Reuse
- Updatable Views and INSTEAD OF Triggers
- Materialized Views and REFRESH Strategies
- When to Pre-Aggregate