PostgreSQL 视图

视图（View）是一个虚拟表，它的内容由 SELECT 查询定义。视图不存储数据，每次访问视图时，PostgreSQL 会执行定义视图的查询语句。视图可以简化复杂查询、提供数据安全性和逻辑独立性。

什么是视图？

视图本质上是一个存储的查询语句。当你查询视图时，PostgreSQL 会将视图定义的查询与你对视图的查询合并，然后执行合并后的查询。

视图的优点

1. 简化复杂查询：将复杂的 SQL 查询封装成视图，后续可以像表一样简单查询
2. 数据安全：只暴露必要的数据列，隐藏敏感字段
3. 逻辑独立：修改底层表结构时，应用程序只需修改视图定义
4. 数据一致性：确保所有用户看到相同的数据格式

创建视图

基本语法

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

简单视图示例

假设有以下订单系统表：

-- 客户表
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10, 2),
    status VARCHAR(20) DEFAULT 'pending'
);

-- 插入测试数据
INSERT INTO customers (name, email, city) VALUES
    ('张三', '[email protected]', '北京'),
    ('李四', '[email protected]', '上海'),
    ('王五', '[email protected]', '广州');

INSERT INTO orders (customer_id, total_amount, status) VALUES
    (1, 299.99, 'completed'),
    (1, 159.00, 'pending'),
    (2, 599.00, 'completed'),
    (3, 89.50, 'cancelled');

创建一个客户订单汇总视图：

CREATE VIEW customer_orders AS
SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    c.email,
    c.city,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END), 0) AS completed_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email, c.city;

-- 查询视图
SELECT * FROM customer_orders;

输出示例：

 customer_id | customer_name |        email         | city  | order_count | total_spent | completed_amount
-------------+---------------+----------------------+-------+-------------+-------------+-----------------
           1 | 张三          | [email protected] | 北京  |           2 |      458.99 |           299.99
           2 | 李四          | [email protected]     | 上海  |           1 |      599.00 |           599.00
           3 | 王五          | [email protected]   | 广州  |           1 |       89.50 |             0.00

视图中的计算字段

-- 创建产品销售统计视图
CREATE VIEW product_sales_stats AS
SELECT 
    p.id,
    p.name AS product_name,
    p.price,
    COUNT(oi.id) AS times_ordered,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    ROUND(AVG(oi.quantity * oi.unit_price), 2) AS avg_order_value
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.price;

查看视图

查看视图定义

-- 使用 \d 命令
\d customer_orders

-- 查询系统表
SELECT 
    viewname,
    definition
FROM pg_views 
WHERE schemaname = 'public';

-- 使用 information_schema
SELECT 
    table_name,
    view_definition
FROM information_schema.views 
WHERE table_schema = 'public';

可更新视图

PostgreSQL 支持对简单视图进行 INSERT、UPDATE、DELETE 操作，这些操作会自动传递到底层基础表。

自动可更新视图的条件

视图必须满足以下条件才能自动可更新：

1. 视图的 FROM 子句中只能有一个表或可更新视图
2. SELECT 列表中不能包含聚合函数（SUM、COUNT 等）
3. SELECT 列表中不能包含 DISTINCT
4. SELECT 列表中不能包含窗口函数
5. SELECT 列表中不能包含集合操作（UNION、INTERSECT、EXCEPT）
6. 不能包含 GROUP BY、HAVING、LIMIT、OFFSET

可更新视图示例

-- 创建一个简单的可更新视图
CREATE VIEW active_customers AS
SELECT id, name, email, city
FROM customers
WHERE city = '北京';

-- 通过视图插入数据（会插入到 customers 表）
INSERT INTO active_customers (name, email, city) 
VALUES ('赵六', '[email protected]', '北京');

-- 通过视图更新数据
UPDATE active_customers 
SET email = '[email protected]' 
WHERE name = '张三';

-- 通过视图删除数据
DELETE FROM active_customers WHERE name = '赵六';

WITH CHECK OPTION

使用 WITH CHECK OPTION 可以确保通过视图插入或更新的数据必须满足视图的 WHERE 条件：

-- 创建带检查选项的视图
CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, city
FROM customers
WHERE city = '北京'
WITH CHECK OPTION;

-- 这条插入会成功
INSERT INTO active_customers (name, email, city) 
VALUES ('测试用户', '[email protected]', '北京');

-- 这条插入会失败（城市不满足条件）
INSERT INTO active_customers (name, email, city) 
VALUES ('测试用户2', '[email protected]', '上海');
-- 错误: 新行违反了视图的 CHECK OPTION

物化视图（Materialized View）

物化视图是将查询结果实际存储在磁盘上的视图。与普通视图不同，物化视图的数据是持久化的，需要手动刷新。

创建物化视图

-- 创建物化视图
CREATE MATERIALIZED VIEW order_summary AS
SELECT 
    DATE(order_date) AS order_day,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS daily_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY DATE(order_date);

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

刷新物化视图

-- 完全刷新（删除旧数据，重新查询）
REFRESH MATERIALIZED VIEW order_summary;

-- 并发刷新（不阻塞查询，需要唯一索引）
CREATE UNIQUE INDEX idx_order_summary_day ON order_summary(order_day);
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

物化视图的使用场景

1. 报表查询：定期生成统计报表
2. 数据仓库：预计算复杂的聚合查询
3. 缓存查询结果：减少重复计算

-- 销售报表物化视图
CREATE MATERIALIZED VIEW monthly_sales_report AS
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

-- 创建索引提高查询性能
CREATE INDEX idx_monthly_sales ON monthly_sales_report(year, month);

-- 每天刷新一次
REFRESH MATERIALIZED VIEW monthly_sales_report;

普通视图 vs 物化视图

特性	普通视图	物化视图
数据存储	不存储	存储实际数据
查询性能	每次执行查询	直接读取缓存数据
数据新鲜度	实时	需要手动刷新
适用场景	简化查询、安全控制	报表、聚合查询

修改和删除视图

修改视图

-- 使用 CREATE OR REPLACE 修改视图
CREATE OR REPLACE VIEW customer_orders AS
SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    c.email,
    c.city,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.order_date) AS last_order_date  -- 新增字段
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email, c.city;

-- 使用 ALTER VIEW 修改视图属性
ALTER VIEW customer_orders SET (security_barrier = true);

-- 重命名视图
ALTER VIEW customer_orders RENAME TO customer_order_summary;

-- 重命名视图列
ALTER VIEW customer_order_summary RENAME COLUMN customer_name TO name;

删除视图

-- 删除视图
DROP VIEW IF EXISTS customer_order_summary;

-- 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS order_summary;

-- 级联删除（同时删除依赖对象）
DROP VIEW customer_orders CASCADE;

视图与安全

安全屏障视图

安全屏障视图可以隐藏底层表的数据，防止用户通过 WHERE 条件推断出不该看到的数据：

-- 创建安全屏障视图
CREATE VIEW employee_salaries WITH (security_barrier = true) AS
SELECT id, name, department
FROM employees
WHERE department = 'IT';

-- 设置视图权限
GRANT SELECT ON employee_salaries TO hr_user;
-- 用户 hr_user 只能看到 IT 部门的员工

列级安全

-- 创建隐藏敏感列的视图
CREATE VIEW customer_public AS
SELECT id, name, city
FROM customers;

-- 授权给普通用户
GRANT SELECT ON customer_public TO app_user;
-- app_user 无法看到 email 和 phone

视图的最佳实践

1. 命名规范

-- 使用描述性名称
CREATE VIEW v_active_orders AS ...;
CREATE VIEW mv_monthly_stats AS ...;  -- 物化视图前缀

-- 或使用后缀
CREATE VIEW orders_active AS ...;
CREATE VIEW stats_monthly_materialized AS ...;

2. 性能考虑

-- 避免在视图中嵌套多层视图
-- 不好的做法
CREATE VIEW view1 AS SELECT ...;
CREATE VIEW view2 AS SELECT * FROM view1;
CREATE VIEW view3 AS SELECT * FROM view2;  -- 优化困难

-- 好的做法：直接编写完整查询
CREATE VIEW final_report AS
SELECT ... FROM base_tables;

3. 使用物化视图的场景

-- 复杂聚合查询，数据更新频率低
CREATE MATERIALIZED VIEW daily_statistics AS
SELECT 
    date_trunc('day', created_at) AS day,
    COUNT(*) AS new_users,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_users
FROM users
GROUP BY date_trunc('day', created_at);

-- 定期刷新（如每天凌晨）
-- crontab: 0 0 * * * psql -c "REFRESH MATERIALIZED VIEW daily_statistics;"

小结

本章我们学习了：

1. 视图的概念：虚拟表，存储查询定义
2. 创建视图：基本语法和复杂查询视图
3. 可更新视图：直接通过视图修改数据
4. 物化视图：存储实际数据的视图，需要手动刷新
5. 视图安全：安全屏障视图和列级权限控制
6. 视图管理：修改、删除视图

练习

1. 创建一个视图，显示每个客户的订单数量和总消费金额
2. 创建一个物化视图，统计每日销售数据
3. 创建一个可更新视图，只显示特定状态的订单
4. 使用 WITH CHECK OPTION 确保数据完整性

参考资源

• PostgreSQL 视图官方文档
• PostgreSQL 物化视图官方文档