Advanced Views & Virtual Tables

-- UPDATABLE VIEWS: INSERT/UPDATE/DELETE through a view

-- Simple updatable view (automatically updatable):
CREATE VIEW v_active_employees AS
SELECT id, name, email, department, salary
FROM employees
WHERE status = 'active';

-- You can INSERT through the view:
INSERT INTO v_active_employees (name, email, department, salary)
VALUES ('New Hire', 'new@company.com', 'Engineering', 85000);
-- Inserts into the underlying employees table

-- UPDATE through the view:
UPDATE v_active_employees SET sala
...

-- SECURITY VIEWS: control data access through views

-- View that masks sensitive data:
CREATE VIEW v_customer_directory AS
SELECT
    id,
    name,
    LEFT(email, 3) || '***@' || SPLIT_PART(email, '@', 2) AS email,
    city,
    state,
    -- Hide exact address
    city || ', ' || state AS location
FROM customers;

-- Grant view access, deny table access:
REVOKE ALL ON customers FROM app_readonly;
GRANT SELECT ON v_customer_directory TO app_readonly;

-- Security barrier view (prevents optimi
...

-- RECURSIVE VIEWS: encapsulate recursive CTEs

CREATE RECURSIVE VIEW v_org_chart (
    employee_id, employee_name, manager_id, depth, path
) AS
-- Anchor: CEO (no manager)
SELECT id, name, manager_id, 0,
       ARRAY[name::text]
FROM employees WHERE manager_id IS NULL

UNION ALL

-- Recursive: employees under each manager
SELECT e.id, e.name, e.manager_id, oc.depth + 1,
       oc.path || e.name::text
FROM employees e
JOIN v_org_chart oc ON e.manager_id = oc.employee_id;

-- Now anyone can query
...

-- INSTEAD OF triggers: make complex views updatable

-- View joining multiple tables (not auto-updatable):
CREATE VIEW v_order_details AS
SELECT o.id AS order_id, o.order_date, o.status,
       c.name AS customer_name, c.email,
       oi.product_id, p.name AS product_name,
       oi.quantity, oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- INSTEAD OF trigger handles INSERT on the view:
CR
...

-- API VIEWS: stable interface for applications

-- Version your views to avoid breaking changes:
CREATE VIEW api.v1_products AS
SELECT id, name, price, category, status
FROM products
WHERE status = 'active';

CREATE VIEW api.v2_products AS
SELECT id, name, price, category, status,
       description,              -- new in v2
       created_at,               -- new in v2
       COALESCE(discount_pct, 0) AS discount  -- renamed in v2
FROM products
WHERE status = 'active';

-- Old applications us
...