Views

Chapter 33: Views - Virtual Tables

Creating and Managing Views

---

33.1 Understanding Views

Views are virtual tables based on the result of a query. They don’t store data but provide a way to simplify complex queries.

                    View Types in PostgreSQL
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                        View Types                                    │
    │                                                                      │
    │   Regular Views:                                                    │
    │   ─────────────                                                     │
    │   • Stored query definition                                         │
    │   • Always reflects current data                                    │
    │   • Cannot be indexed                                               │
    │   • Updates may have limitations                                    │
    │                                                                      │
    │   Materialized Views:                                               │
    │   ─────────────────                                                 │
    │   • Stores physical copy of query result                           │
    │   • Must be refreshed to update data                               │
    │   • Can have indexes                                               │
    │   • Great for slow queries with aggregation                        │
    │                                                                      │
    │   Temporary Views:                                                  │
    │   ──────────────                                                   │
    │   • Exists only for current session                                │
    │   • Good for complex queries in one session                         │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

33.2 Creating Basic Views

-- Create simple view
CREATE VIEW active_users AS
SELECT
    user_id,
    username,
    email,
    created_at
FROM users
WHERE status = 'active';

-- Query the view like a table
SELECT * FROM active_users;

-- Create view with complex joins
CREATE VIEW user_order_summary AS
SELECT
    u.user_id,
    u.username,
    u.email,
    COUNT(o.order_id) as total_orders,
    COALESCE(SUM(o.total), 0) as total_spent,
    COALESCE(AVG(o.total), 0) as avg_order_value,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username, u.email;

-- Create view with calculated columns
CREATE VIEW product_catalog AS
SELECT
    p.product_id,
    p.product_name,
    p.price,
    c.category_name,
    p.stock_quantity,
    CASE
        WHEN p.stock_quantity = 0 THEN 'Out of Stock'
        WHEN p.stock_quantity < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END as stock_status,
    p.price * 1.2 as retail_price
FROM products p
JOIN categories c ON p.category_id = c.category_id;

---

33.3 Updating Views

Not all views are updatable. PostgreSQL has rules to determine updatability.

-- Simple updatable view
CREATE VIEW simple_users AS
SELECT user_id, username, email
FROM users;

-- Insert through view (if all columns present)
INSERT INTO simple_users (username, email)
VALUES ('new_user', 'new@example.com');

-- Update through view
UPDATE simple_users
SET email = 'updated@example.com'
WHERE username = 'new_user';

-- Delete through view
DELETE FROM simple_users WHERE username = 'new_user';

-- Create view with check option
CREATE VIEW active_products AS
SELECT
    product_id,
    product_name,
    price,
    status
FROM products
WHERE status = 'active'
WITH CHECK OPTION;

-- This will fail - can't insert inactive product
INSERT INTO active_products (product_name, price, status)
VALUES ('Test Product', 10.00, 'inactive');

-- This will succeed
INSERT INTO active_products (product_name, price, status)
VALUES ('Test Product', 10.00, 'active');

---

33.4 Recursive Views

Views can reference themselves (useful for hierarchical data).

-- Create employee hierarchy view
CREATE VIEW employee_hierarchy AS
WITH RECURSIVE org_chart AS (
    -- Base case: top-level employees
    SELECT
        employee_id,
        manager_id,
        first_name || ' ' || last_name as employee_name,
        department,
        1 as level,
        first_name || ' ' || last_name as path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT
        e.employee_id,
        e.manager_id,
        e.first_name || ' ' || e.last_name,
        e.department,
        oc.level + 1,
        oc.path || ' > ' || e.first_name || ' ' || e.last_name
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

-- Query hierarchical view
SELECT * FROM employee_hierarchy ORDER BY level, employee_id;

---

33.5 Security with Views

Views can hide sensitive data and simplify permissions.

-- Create view for customer service (limited data)
CREATE VIEW customer_service_view AS
SELECT
    user_id,
    username,
    email,
    phone,
    created_at,
    status
FROM users;

-- Grant access to customer service role
GRANT SELECT ON customer_service_view TO customer_service_role;

-- Create view hiding sensitive columns
CREATE VIEW public_product_info AS
SELECT
    product_id,
    product_name,
    category_name,
    price,
    description
FROM products p
JOIN categories c ON p.category_id = c.category_id;

-- Create view with aggregated data only
CREATE VIEW sales_summary AS
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as revenue,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Create secure view using security_barrier
CREATE SECURE VIEW sensitive_data AS
SELECT * FROM users
WHERE user_id = current_user_id();  -- Row-level security

---

33.6 Managing Views

-- List all views in database
SELECT
    table_name,
    table_type
FROM information_schema.views
WHERE table_schema = 'public';

-- Get view definition
SELECT pg_get_viewdef('view_name'::regclass, true);

-- Replace view (alter definition)
CREATE OR REPLACE VIEW active_users AS
SELECT
    user_id,
    username,
    email,
    created_at,
    status
FROM users
WHERE status = 'active'
AND created_at > '2024-01-01';

-- Rename view
ALTER VIEW active_users RENAME TO current_users;

-- Drop view
DROP VIEW IF EXISTS old_view;

-- Drop view with CASCADE (drops dependent objects)
-- DROP VIEW my_view CASCADE;

---

Summary

Feature	Description
Regular View	Virtual table, always current
Updatable	Can INSERT/UPDATE/DELETE
CHECK OPTION	Enforce WHERE clause
Security	Control data access

---

Next: Chapter 34: Materialized Views