PostgreSQL 视图 #

┌─────────────────────────────────────────────────────────────┐
│                    视图类型                                  │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  普通视图（VIEW）                                           │
│  ├── 不存储数据                                            │
│  ├── 每次查询执行底层 SQL                                  │
│  ├── 实时反映基表变化                                      │
│  └── 适合简化复杂查询                                      │
│                                                             │
│  物化视图（MATERIALIZED VIEW）                              │
│  ├── 存储查询结果                                          │
│  ├── 需要手动刷新                                          │
│  ├── 查询速度快                                            │
│  └── 适合数据仓库、报表                                    │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建示例表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

INSERT INTO departments (name) VALUES ('Engineering'), ('Sales'), ('Marketing');
INSERT INTO employees (name, department_id, salary, hire_date) VALUES
    ('Alice', 1, 90000.00, '2020-01-15'),
    ('Bob', 1, 80000.00, '2021-03-20'),
    ('Charlie', 2, 85000.00, '2019-06-10'),
    ('Diana', 2, 70000.00, '2022-02-28'),
    ('Eve', 3, 65000.00, '2021-08-05');

-- 创建简单视图
CREATE VIEW employee_list AS
SELECT 
    e.id,
    e.name,
    d.name AS department,
    e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 查询视图
SELECT * FROM employee_list;

-- 输出：
--  id |  name   | department  | salary
-- ----+---------+-------------+---------
--   1 | Alice   | Engineering | 90000.00
--   2 | Bob     | Engineering | 80000.00
--   3 | Charlie | Sales       | 85000.00
--   4 | Diana   | Sales       | 70000.00
--   5 | Eve     | Marketing   | 65000.00

-- 创建返回特定部门员工的函数
CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_name VARCHAR)
RETURNS TABLE (
    id INTEGER,
    name VARCHAR,
    salary DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.id, e.name, e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE d.name = dept_name;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT * FROM get_employees_by_dept('Engineering');

-- 创建基础视图
CREATE VIEW employee_base AS
SELECT 
    id,
    name,
    department_id,
    salary,
    hire_date
FROM employees
WHERE is_active = TRUE;

-- 创建嵌套视图
CREATE VIEW employee_summary AS
SELECT 
    e.id,
    e.name,
    d.name AS department,
    e.salary,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.hire_date))::INTEGER AS years_of_service
FROM employee_base e
LEFT JOIN departments d ON e.department_id = d.id;

-- 替换视图定义
CREATE OR REPLACE VIEW employee_list AS
SELECT 
    e.id,
    e.name,
    d.name AS department,
    e.salary,
    e.hire_date
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 修改视图名称
ALTER VIEW employee_list RENAME TO employee_overview;

-- 修改视图所有者
ALTER VIEW employee_list OWNER TO new_owner;

-- 设置视图选项
ALTER VIEW employee_list SET (security_barrier = true);

-- 删除视图
DROP VIEW employee_list;

-- 如果存在则删除
DROP VIEW IF EXISTS employee_list;

-- 级联删除依赖对象
DROP VIEW employee_list CASCADE;

-- 简单视图可以更新
CREATE VIEW active_employees AS
SELECT id, name, salary, department_id
FROM employees
WHERE is_active = TRUE;

-- 插入数据
INSERT INTO active_employees (name, salary, department_id)
VALUES ('Frank', 75000.00, 1);

-- 更新数据
UPDATE active_employees SET salary = 95000.00 WHERE name = 'Alice';

-- 删除数据
DELETE FROM active_employees WHERE name = 'Frank';

┌─────────────────────────────────────────────────────────────┐
│                    可更新视图条件                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  视图必须满足以下条件才能更新：                              │
│                                                             │
│  ✅ FROM 子句只有一个表                                    │
│  ✅ 不包含 DISTINCT                                        │
│  ✅ 不包含 GROUP BY                                        │
│  ✅ 不包含 HAVING                                          │
│  ✅ 不包含 LIMIT/OFFSET                                    │
│  ✅ 不包含 UNION/INTERSECT/EXCEPT                          │
│  ✅ 不包含聚合函数                                          │
│  ✅ 不包含窗口函数                                          │
│  ✅ 不包含子查询（FROM 中的除外）                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建复杂视图
CREATE VIEW employee_department AS
SELECT 
    e.id,
    e.name AS employee_name,
    e.salary,
    d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 创建 INSTEAD OF 触发器函数
CREATE OR REPLACE FUNCTION update_employee_department()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO employees (name, salary, department_id)
        VALUES (
            NEW.employee_name,
            NEW.salary,
            (SELECT id FROM departments WHERE name = NEW.department_name)
        );
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE employees SET
            name = NEW.employee_name,
            salary = NEW.salary,
            department_id = (SELECT id FROM departments WHERE name = NEW.department_name)
        WHERE id = OLD.id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM employees WHERE id = OLD.id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_employee_department
    INSTEAD OF INSERT OR UPDATE OR DELETE ON employee_department
    FOR EACH ROW
    EXECUTE FUNCTION update_employee_department();

-- 现在可以更新视图
INSERT INTO employee_department (employee_name, salary, department_name)
VALUES ('George', 70000.00, 'Engineering');

UPDATE employee_department 
SET salary = 80000.00 
WHERE employee_name = 'George';

-- 创建物化视图
CREATE MATERIALIZED VIEW employee_stats AS
SELECT 
    d.name AS department,
    COUNT(*) AS employee_count,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name;

-- 查询物化视图
SELECT * FROM employee_stats;

-- 输出：
--  department  | employee_count | avg_salary | max_salary | min_salary
-- -------------+----------------+------------+------------+------------
--  Engineering |              2 |   85000.00 |   90000.00 |   80000.00
--  Sales       |              2 |   77500.00 |   85000.00 |   70000.00
--  Marketing   |              1 |   65000.00 |   65000.00 |   65000.00

-- 刷新物化视图
REFRESH MATERIALIZED VIEW employee_stats;

-- 并发刷新（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY employee_stats;

-- 注意：CONCURRENTLY 需要物化视图有唯一索引
CREATE UNIQUE INDEX idx_employee_stats_dept ON employee_stats(department);

-- 创建带存储参数的物化视图
CREATE MATERIALIZED VIEW employee_stats_with_data 
WITH (fillfactor = 90) AS
SELECT * FROM employee_stats
WITH DATA;

-- 创建不带数据的物化视图
CREATE MATERIALIZED VIEW employee_stats_empty AS
SELECT * FROM employee_stats
WITH NO DATA;

-- 需要手动刷新才能查询
REFRESH MATERIALIZED VIEW employee_stats_empty;

-- 删除物化视图
DROP MATERIALIZED VIEW employee_stats;

-- 如果存在则删除
DROP MATERIALIZED VIEW IF EXISTS employee_stats;

-- 复杂查询
SELECT 
    e.name,
    d.name AS department,
    e.salary,
    CASE 
        WHEN e.salary > dept_avg.avg_salary THEN 'Above Average'
        ELSE 'Below Average'
    END AS salary_status
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id;

-- 创建视图简化
CREATE VIEW employee_salary_analysis AS
SELECT 
    e.name,
    d.name AS department,
    e.salary,
    ROUND(dept_avg.avg_salary, 2) AS dept_avg_salary,
    CASE 
        WHEN e.salary > dept_avg.avg_salary THEN 'Above Average'
        ELSE 'Below Average'
    END AS salary_status
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id;

-- 使用视图
SELECT * FROM employee_salary_analysis WHERE salary_status = 'Above Average';

-- 创建只显示部分列的视图
CREATE VIEW employee_public AS
SELECT id, name, department_id FROM employees;

-- 授予视图访问权限
GRANT SELECT ON employee_public TO public_user;

-- 撤销基表访问权限
REVOKE ALL ON employees FROM public_user;

-- 隐藏复杂的业务逻辑
CREATE VIEW customer_orders AS
SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent,
    MAX(o.created_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- 查看所有视图
SELECT 
    schemaname,
    viewname,
    viewowner
FROM pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

-- 查看视图定义
SELECT pg_get_viewdef('employee_list'::regclass, true);

-- 查看物化视图
SELECT 
    schemaname,
    matviewname,
    matviewowner
FROM pg_matviews;

-- 使用 psql 命令
\d+ employee_list
\dm+ employee_stats

高级阶段
├── 索引
├── 视图（本文）
├── 存储过程
├── 触发器
└── 事务与锁