Views 與 Materialized Views：PostgreSQL 的虛擬關係與物化快取 | PostgreSQL

-- 查看視圖的重寫規則
SELECT rulename, ev_type, is_instead, ev_qual, ev_action
FROM pg_rewrite
WHERE ev_class = 'my_view'::regclass;

-- 基本 View 建立
CREATE VIEW active_users AS
SELECT
    id,
    username,
    email,
    created_at
FROM users
WHERE is_active = TRUE
  AND deleted_at IS NULL;

-- 帶欄位別名的 View
CREATE VIEW order_summary (order_id, customer_name, total_amount, status) AS
SELECT
    o.id,
    c.full_name,
    SUM(oi.price * oi.quantity),
    o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.full_name, o.status;

-- CREATE OR REPLACE VIEW：替換現有 View（欄位名稱與類型必須相容）
CREATE OR REPLACE VIEW active_users AS
SELECT
    id,
    username,
    email,
    last_login_at,   -- 新增欄位（只能追加，不能刪除或改變既有欄位類型）
    created_at
FROM users
WHERE is_active = TRUE
  AND deleted_at IS NULL;

-- 刪除 View
DROP VIEW IF EXISTS active_users;
DROP VIEW IF EXISTS active_users CASCADE;  -- 同時刪除相依物件

-- 定義處理函數
CREATE OR REPLACE FUNCTION fn_insert_employee_view()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO employees (name, department_id, salary)
    VALUES (NEW.name, NEW.department_id, NEW.salary)
    RETURNING id INTO NEW.id;
    RETURN NEW;
END;
$$;

-- 在 View 上建立 INSTEAD OF Trigger
CREATE TRIGGER trg_insert_employee_view
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
EXECUTE FUNCTION fn_insert_employee_view();

-- 建立帶 CHECK OPTION 的 View
CREATE OR REPLACE VIEW active_employees AS
SELECT id, name, department_id, is_active
FROM employees
WHERE is_active = TRUE
WITH CHECK OPTION;

-- 以下 UPDATE 會失敗：更新後的列不再符合 is_active = TRUE
UPDATE active_employees SET is_active = FALSE WHERE id = 1;
-- ERROR: new row violates check option for view "active_employees"

-- 巢狀 View 場景
CREATE VIEW eng_employees AS
SELECT * FROM active_employees WHERE department_id = 10;

-- LOCAL：只檢查自身 View 的條件
CREATE OR REPLACE VIEW eng_employees AS
SELECT * FROM active_employees WHERE department_id = 10
WITH LOCAL CHECK OPTION;

-- CASCADED（預設）：同時檢查所有上層 View 的條件
CREATE OR REPLACE VIEW eng_employees AS
SELECT * FROM active_employees WHERE department_id = 10
WITH CASCADED CHECK OPTION;

-- 表格擁有者：admin，普通用戶：app_user
CREATE VIEW public.salary_summary
SECURITY DEFINER AS
SELECT department_id, AVG(salary) AS avg_salary
FROM hr.salaries   -- app_user 本無權存取
GROUP BY department_id;

GRANT SELECT ON public.salary_summary TO app_user;
-- app_user 可查詢聚合資料，但無法直接存取 hr.salaries

-- PostgreSQL 15+ 語法
CREATE VIEW orders_view
WITH (security_invoker = true) AS
SELECT id, amount, status, created_at
FROM orders;

-- 建立 Materialized View 並立即填充資料
CREATE MATERIALIZED VIEW sales_monthly AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at), product_id
WITH DATA;  -- 立即填充（預設）

-- 建立時不填充（稍後 REFRESH）
CREATE MATERIALIZED VIEW sales_monthly AS
... WITH NO DATA;

-- 刷新（鎖表，查詢期間無法讀取）
REFRESH MATERIALIZED VIEW sales_monthly;

-- 並行刷新（不鎖表，查詢期間仍可讀取舊資料）
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_monthly;

-- 必須先建立 Unique Index
CREATE UNIQUE INDEX ON sales_monthly (month, product_id);

-- 之後才能使用 CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_monthly;

CREATE MATERIALIZED VIEW mv_sales_report AS
SELECT
    DATE_TRUNC('day', o.created_at) AS sale_date,
    c.region,
    p.category_id,
    p.id AS product_id,
    p.name AS product_name,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.price) AS revenue,
    COUNT(DISTINCT o.id) AS order_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY DATE_TRUNC('day', o.created_at), c.region,
         p.category_id, p.id, p.name
WITH DATA;

-- 支援 REFRESH CONCURRENTLY 的唯一索引（必要）
CREATE UNIQUE INDEX idx_mv_sales_pk
ON mv_sales_report (sale_date, region, product_id);

-- 按日期範圍查詢
CREATE INDEX idx_mv_sales_date
ON mv_sales_report (sale_date DESC);

-- 按地區篩選
CREATE INDEX idx_mv_sales_region
ON mv_sales_report (region, sale_date DESC);

-- 高收入商品排行
CREATE INDEX idx_mv_sales_revenue
ON mv_sales_report (revenue DESC);

-- 部分索引：只為熱門地區建立
CREATE INDEX idx_mv_sales_tw
ON mv_sales_report (sale_date DESC, product_id)
WHERE region = 'TW';

-- 內部結構可以變動，但 View 提供穩定的 API
CREATE VIEW api.v_users AS
SELECT
    id,
    username,
    email,
    full_name,
    avatar_url,
    created_at,
    -- 敏感欄位（password_hash、phone）刻意排除
    CASE WHEN is_premium THEN 'premium' ELSE 'free' END AS account_type
FROM internal.users
WHERE deleted_at IS NULL;

GRANT SELECT ON api.v_users TO web_role;

-- 配合 SECURITY INVOKER 與 RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_orders_policy ON orders
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

CREATE VIEW my_orders
WITH (security_invoker = true) AS
SELECT id, amount, status, created_at
FROM orders;

-- 將多表 JOIN 封裝為 View
CREATE VIEW v_order_detail AS
SELECT
    o.id AS order_id,
    o.created_at,
    c.full_name AS customer_name,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'product', p.name,
            'quantity', oi.quantity,
            'price', oi.price
        )
        ORDER BY oi.id
    ) AS items,
    SUM(oi.quantity * oi.price) AS subtotal,
    o.shipping_fee,
    SUM(oi.quantity * oi.price) + o.shipping_fee AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id, o.created_at, c.full_name, o.shipping_fee;

-- 應用程式只需簡單查詢
SELECT * FROM v_order_detail WHERE order_id = 12345;

CREATE EXTENSION pg_cron;

-- 每天凌晨 2 點刷新報表型物化視圖
SELECT cron.schedule(
    'refresh-sales-monthly',
    '0 2 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_monthly'
);

-- 每 10 分鐘刷新即時性較高的物化視圖
SELECT cron.schedule(
    'refresh-product-stats',
    '*/10 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY product_stats'
);

-- 查看已排程的任務
SELECT * FROM cron.job;

-- 在底層表格的 DML 操作後觸發刷新（適合低頻更新場景）
CREATE OR REPLACE FUNCTION refresh_product_stats()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY product_stats;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_refresh_product_stats
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_product_stats();
-- 注意：高頻寫入時會成為效能瓶頸，建議加入節流機制

-- 安裝 pg_ivm 擴展
CREATE EXTENSION IF NOT EXISTS pg_ivm;

-- 建立增量物化視圖（IMMV）
SELECT create_immv(
    'sales_summary_immv',
    'SELECT product_id, SUM(quantity) AS total_qty, SUM(amount) AS total_amount
     FROM orders
     GROUP BY product_id'
);

-- IMMV 在底層表格 DML 操作時自動增量更新
INSERT INTO orders (product_id, quantity, amount) VALUES (1, 5, 500);
-- sales_summary_immv 自動更新 product_id = 1 的列

-- 危險：底層表格新增欄位後，View 不會自動更新
CREATE VIEW user_info AS SELECT * FROM users;

ALTER TABLE users ADD COLUMN bio TEXT;
-- user_info 仍然看不到 bio 欄位！

-- 正確做法：明確列出所需欄位
CREATE VIEW user_info AS
SELECT id, username, email, created_at FROM users;

-- 危險：攻擊者可能透過修改 search_path 影響 View 行為
CREATE VIEW risky_view SECURITY DEFINER AS
SELECT * FROM users;

-- 安全做法：使用完整 Schema 路徑
CREATE VIEW safe_view SECURITY DEFINER AS
SELECT * FROM public.users;