48 CREATE VIEW

-- A simple view: customer + their order count
CREATE OR REPLACE VIEW customer_orders AS
SELECT c.id, c.name, count(o.id) AS order_count, COALESCE(sum(o.total), 0) AS total_spent
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Use it like a table
SELECT * FROM customer_orders WHERE total_spent > 0 ORDER BY total_spent DESC;

-- Simple updatable view (no joins, no aggregates, no DISTINCT)
CREATE OR REPLACE VIEW alice_orders AS
SELECT * FROM orders_example WHERE customer_id = 1;

-- You can INSERT through an updatable view
INSERT INTO alice_orders (id, customer_id, total) VALUES (10, 1, 99.00);

-- WITH CHECK OPTION: refuse INSERTs/UPDATEs that wouldn't satisfy the view's WHERE
CREATE OR REPLACE VIEW alice_orders_strict AS
SELECT * FROM orders_example WHERE customer_id = 1
WITH CHECK OPTION;

-- This would fail because customer_id = 2 is not in the view's scope
-- INSERT INTO alice_orders_strict (id, customer_id, total) VALUES (11, 2, 1.00);

-- List views
SELECT viewname, definition
FROM pg_views
WHERE schemaname = 'public'
ORDER BY viewname;

-- Drop a view
DROP VIEW IF EXISTS alice_orders;
DROP VIEW IF EXISTS alice_orders_strict;
DROP VIEW IF EXISTS customer_orders;