Creating and Using Views

CREATE VIEW active_customers AS
SELECT id, full_name, email FROM customers WHERE status = 'active';

CREATE VIEW customer_summary(id, name, total_orders) AS
SELECT c.id, c.full_name, count(o.id)
  FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
 GROUP BY c.id, c.full_name;

SELECT * FROM active_customers WHERE email LIKE '%@example.com';

CREATE VIEW open_orders AS
SELECT id, customer_id, status, total_cents FROM orders WHERE status <> 'cancelled';
UPDATE open_orders SET status='shipped' WHERE id = 1001;

CREATE VIEW us_customers AS
SELECT * FROM customers WHERE country = 'US'
WITH CASCADED CHECK OPTION;
-- inserts/updates that don't satisfy country='US' will fail

CREATE RECURSIVE VIEW org(id, name, depth) AS
   SELECT id, name, 1 FROM employees WHERE manager_id IS NULL
   UNION ALL
   SELECT e.id, e.name, o.depth+1
     FROM employees e JOIN org o ON e.manager_id = o.id;

CREATE OR REPLACE VIEW active_customers AS
SELECT id, full_name, email, country FROM customers WHERE status='active';

ALTER VIEW active_customers ALTER COLUMN email SET DEFAULT '';
ALTER VIEW active_customers OWNER TO app_owner;
ALTER VIEW active_customers SET SCHEMA reporting;

ALTER VIEW active_customers RENAME TO active_customer_v;

\dv
SELECT table_schema, table_name FROM information_schema.views;

DROP VIEW IF EXISTS active_customers CASCADE;

CREATE VIEW my_orders WITH (security_barrier=true) AS
SELECT * FROM orders WHERE customer_id = current_setting('app.user_id')::int;