Advanced Views & Virtual Tables

-- A view is a saved SELECT that behaves like a virtual table.
-- It stores no data of its own — it re-runs its query every time.
CREATE VIEW v_in_stock AS
SELECT id, product_name, category, price, stock
FROM products
WHERE stock > 0;

-- Query it exactly like a table:
SELECT product_name, stock FROM v_in_stock;

-- Because this view reads ONE table and adds no GROUP BY,
-- DISTINCT, or aggregates, the database marks it "auto-updatable":
-- you can INSERT / UPDATE / DELETE through it.

-- UPDATE through the view — it rewrites the real products row.
UPDATE v_in_stock
SET price = 21.99
WHERE product_name = 'Wireless Mouse';

-- INSERT through the view — the new row lands in products.
INSERT INTO v_in_stock (product_name, category, price, stock)
VALUES ('Laptop Stand', 'Home', 39.00, 50);

-- DELETE through the view — removes the real row too.
DELETE FROM v_in_stock WHERE product_name = 'Notebook';

-- The view is just a window onto products; every write
-- passes straight throug
...

-- Rebuild the view WITH CHECK OPTION so the filter is enforced
-- on writes, not just reads.
CREATE VIEW v_in_stock AS
SELECT id, product_name, category, price, stock
FROM products
WHERE stock > 0
WITH CHECK OPTION;   -- 👈 the new safety rule

-- This INSERT is REJECTED: stock = 0 fails the view's WHERE,
-- so the row would instantly vanish from the view.
INSERT INTO v_in_stock (product_name, category, price, stock)
VALUES ('Backordered Pen', 'Stationery', 2.00, 0);
-- ERROR: new row violates 
...

-- Views over a JOIN or an aggregate are READ-ONLY.
-- The database can't reverse one summary row back into the
-- individual rows it came from, so writes are refused.
CREATE VIEW v_category_stats AS
SELECT category,
       COUNT(*)     AS items,
       ROUND(AVG(price), 2) AS avg_price,
       SUM(stock)   AS total_stock
FROM products
GROUP BY category;

SELECT * FROM v_category_stats ORDER BY category;

-- Try to write through it and you get:
UPDATE v_category_stats SET avg_price = 0 WHERE cat
...

-- A view over a JOIN of products + orders. Reading is fine;
-- writing needs an INSTEAD OF trigger (next section).
CREATE VIEW v_order_lines AS
SELECT o.id          AS order_id,
       o.order_date,
       p.product_name,
       o.quantity,
       p.price,
       o.quantity * p.price AS line_total
FROM orders o
JOIN products p ON p.id = o.product_id;

SELECT order_id, product_name, quantity, line_total
FROM v_order_lines
ORDER BY order_id;

-- SECURITY VIEW: expose only safe COLUMNS, hide cost & supplier.
-- Staff query the view; the base table stays locked down.
CREATE VIEW v_public_catalog AS
SELECT id, product_name, category, price   -- no cost, no supplier_id
FROM products;

-- Lock the real table, open only the view:
REVOKE ALL ON products       FROM sales_team;
GRANT  SELECT ON v_public_catalog TO sales_team;

-- security_invoker (PostgreSQL 15+): run the view with the
-- CALLER's privileges, so row-level security on products
...

-- INSTEAD OF trigger: make the read-only JOIN view writable.
-- The trigger intercepts the INSERT and writes to the REAL table.
CREATE OR REPLACE FUNCTION fn_insert_order_line()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INT;
BEGIN
    -- Look up the product id from the name the view exposed:
    SELECT id INTO v_product_id
    FROM products WHERE product_name = NEW.product_name;

    -- Insert into the underlying orders table:
    INSERT INTO orders (product_id, quantity, 
...