PostgreSQL View（视图）

下面是一份 PostgreSQL 视图（View） 的 完整实战手册，涵盖 语法、类型、权限、性能、更新、物化视图、安全视图、递归视图、最佳实践与常见陷阱，适合开发、DBA、架构师使用。

一、什么是 View？

视图是一个虚拟表，其内容由 SELECT 查询定义，不存储数据（除物化视图外），每次查询时动态执行。

核心优势

优势	说明
简化复杂查询	封装 `JOIN`、`聚合`
数据安全	隐藏底层表结构、列
逻辑抽象	提供统一接口
权限控制	用户只访问视图
兼容性	模拟旧系统表结构

二、基本语法

CREATE [OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE] VIEW view_name
    [(column_name [, ...])]
AS
    query
[WITH [CASCADE | LOCAL] CHECK OPTION];

三、创建视图示例

1. 简单视图

CREATE VIEW active_users AS
SELECT id, email, created_at
FROM users
WHERE status = 'active';

2. 带计算列 + JOIN

CREATE VIEW order_summary AS
SELECT 
    o.id,
    o.created_at,
    u.email,
    COUNT(oi.id) AS item_count,
    SUM(oi.price * oi.quantity) AS total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY o.id, u.email;

3. 重命名列

CREATE VIEW user_profiles AS
SELECT 
    id,
    email AS login_email,
    full_name AS name,
    created_at AS signup_date
FROM users;

四、查询视图 = 查询表

SELECT * FROM active_users WHERE created_at > '2025-01-01';

EXPLAIN SELECT * FROM order_summary WHERE total_amount > 1000;

视图会被 查询重写（Query Rewrite） 合并到底层查询中。

五、修改与删除

-- 替换视图（保留权限）
CREATE OR REPLACE VIEW active_users AS
SELECT id, email, created_at, last_login
FROM users
WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS order_summary CASCADE;

CASCADE 会删除依赖该视图的对象（如其他视图）

六、可更新视图（Updatable Views）

条件（自动可更新）：

仅包含一个表
无 GROUP BY、HAVING、DISTINCT、JOIN、LIMIT
无聚合函数、窗口函数

CREATE VIEW user_emails AS
SELECT id, email FROM users WHERE status = 'active';

-- 可直接更新
UPDATE user_emails SET email = 'new@example.com' WHERE id = 1;

强制可更新：使用 `INSTEAD OF` 触发器

CREATE VIEW order_summary_updatable AS
SELECT o.id, o.user_id, SUM(oi.quantity) AS total_qty
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.user_id;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_order_summary()
RETURNS TRIGGER AS $$
BEGIN
    -- 自定义逻辑：如不允许更新
    RAISE EXCEPTION 'Cannot update aggregated view';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器
CREATE TRIGGER trg_instead_update
    INSTEAD OF UPDATE ON order_summary_updatable
    FOR EACH ROW
    EXECUTE FUNCTION update_order_summary();

七、`WITH CHECK OPTION`（安全更新）

防止更新后数据“消失”

CREATE VIEW vip_users AS
SELECT * FROM users WHERE membership = 'vip'
WITH CHECK OPTION;

-- 错误：会修改为非 vip
UPDATE vip_users SET membership = 'basic' WHERE id = 1;
-- ERROR: new row violates WITH CHECK OPTION

LOCAL：只检查本视图
CASCADE：检查所有依赖视图（默认）

八、物化视图（Materialized View）

存储查询结果，需手动刷新，适合 高读低写 场景。

语法

CREATE MATERIALIZED VIEW mat_view_name
[WITH [NO] DATA]
AS
    query
[WITH [NO] DATA];

示例

-- 创建并立即填充数据
CREATE MATERIALIZED VIEW daily_sales AS
SELECT 
    DATE(created_at) AS sale_date,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
WITH DATA;

-- 查询（快！）
SELECT * FROM daily_sales;

刷新

-- 完整刷新（重建）
REFRESH MATERIALIZED VIEW daily_sales;

-- 并发刷新（不锁读）
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

-- 仅刷新增量（需索引）
CREATE UNIQUE INDEX ON daily_sales(sale_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

CONCURRENTLY 要求有 唯一索引

九、递归视图（Recursive Views）—— CTE 替代

-- 组织架构：员工汇报关系
CREATE TABLE employees (
    id INT,
    name TEXT,
    manager_id INT
);

-- 递归视图：所有下属
CREATE RECURSIVE VIEW org_chart AS
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id;

-- 查询 CEO 所有下属
SELECT * FROM org_chart;

等价于 WITH RECURSIVE

十、安全视图（Security-Definable View）

结合 行级安全（RLS） 使用

-- 启用 RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 创建策略：只能看自己部门
CREATE POLICY dept_policy ON documents
    USING (department = current_setting('app.current_dept'));

-- 创建视图
CREATE VIEW my_documents AS
SELECT * FROM documents;

-- 用户只能看到自己的
SET app.current_dept = 'HR';
SELECT * FROM my_documents;

十一、权限管理

-- 授予视图查询权限
GRANT SELECT ON order_summary TO analyst_role;

-- 授予更新权限（可更新视图）
GRANT UPDATE ON user_emails TO app_user;

-- 收回
REVOKE SELECT ON order_summary FROM analyst_role;

视图权限 独立于底层表

十二、性能优化

技巧	说明
避免 `SELECT *`	明确列，减少传输
索引底层表	视图依赖表索引
物化视图	高频聚合查询
视图嵌套	谨慎，易导致重复计算
`EXPLAIN` 分析	确认是否下推条件

EXPLAIN SELECT * FROM order_summary WHERE total_amount > 1000;
-- 期望看到 Index Scan

十三、系统表查询

-- 查看所有视图
SELECT schemaname, viewname, viewowner
FROM pg_views
WHERE schemaname = 'public';

-- 查看视图定义
SELECT definition
FROM pg_views
WHERE viewname = 'order_summary';

-- 查看依赖关系
SELECT * FROM pg_depend 
WHERE refobjid = 'order_summary'::regclass;

十四、常见陷阱与避坑

问题	原因	解决
视图变慢	底层查询无索引	给基表加索引
更新失败	视图不可更新	用触发器或改业务
权限泄露	视图暴露敏感列	限制列 + RLS
物化视图数据旧	未刷新	定时 `REFRESH`
`CONCURRENTLY` 失败	无唯一索引	加唯一索引

十五、最佳实践脚本

1. 标准视图模板

CREATE OR REPLACE VIEW v_user_activity AS
SELECT 
    u.id,
    u.email,
    COUNT(o.id) AS order_count,
    MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.email;

-- 授予权限
GRANT SELECT ON v_user_activity TO reporting_role;

-- 添加注释
COMMENT ON VIEW v_user_activity IS '用户订单活动统计视图';

2. 定时刷新物化视图（pg_cron）

-- 安装 pg_cron
-- 配置 crontab
SELECT cron.schedule(
    'refresh-daily-sales',
    '0 2 * * *',  -- 每天 2:00
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales$$
);

十六、速查表

命令	用途
`CREATE VIEW v AS SELECT ...`	创建视图
`CREATE OR REPLACE VIEW`	更新视图
`DROP VIEW v CASCADE`	删除视图
`CREATE MATERIALIZED VIEW`	创建物化视图
`REFRESH MATERIALIZED VIEW CONCURRENTLY`	无锁刷新
`WITH CHECK OPTION`	安全更新
`RECURSIVE VIEW`	递归查询
`GRANT SELECT ON v TO role`	授权

十七、视图 vs 物化视图 vs CTE

特性	普通视图	物化视图	CTE
存储数据	不存	存储	不存
刷新	实时	手动/定时	每次执行
性能	依赖底层	极快	依赖查询
适用	动态数据	报表、缓存	复杂查询

总结：视图使用决策树

graph TD
    A[需要封装查询?] -->|是| B{数据是否频繁变化?}
    B -->|是| C[用普通视图]
    B -->|否| D[用物化视图]
    A -->|否| E[直接写SQL]
    C --> F[加索引 + 权限]
    D --> G[定时刷新 + 唯一索引]

需要我提供一个完整的 “视图 + 物化视图 + 定时刷新 + 权限” 的生产级模板项目吗？

一、什么是 View？

核心优势

二、基本语法

三、创建视图示例

1. 简单视图

2. 带计算列 + JOIN

3. 重命名列

四、查询视图 = 查询表

五、修改与删除

六、可更新视图（Updatable Views）

条件（自动可更新）：

强制可更新：使用 `INSTEAD OF` 触发器

七、`WITH CHECK OPTION`（安全更新）

八、物化视图（Materialized View）

语法

示例

刷新

九、递归视图（Recursive Views）—— CTE 替代

十、安全视图（Security-Definable View）

十一、权限管理

十二、性能优化

十三、系统表查询

十四、常见陷阱与避坑

十五、最佳实践脚本

1. 标准视图模板

2. 定时刷新物化视图（pg_cron）

十六、速查表

十七、视图 vs 物化视图 vs CTE

总结：视图使用决策树

PostgreSQL 教程

PostgreSQL pgAdmin 工具

PostgreSQL ORDER BY 语句

PostgreSQL 常用函数

PostgreSQL DISTINCT 关键字

PostgreSQL WHERE 子句

发表回复取消回复

一、什么是 View？

核心优势

二、基本语法

三、创建视图示例

1. 简单视图

2. 带计算列 + JOIN

3. 重命名列

四、查询视图 = 查询表

五、修改与删除

六、可更新视图（Updatable Views）

条件（自动可更新）：

强制可更新：使用 INSTEAD OF 触发器

七、WITH CHECK OPTION（安全更新）

八、物化视图（Materialized View）

语法

示例

刷新

九、递归视图（Recursive Views）—— CTE 替代

十、安全视图（Security-Definable View）

十一、权限管理

十二、性能优化

十三、系统表查询

十四、常见陷阱与避坑

十五、最佳实践脚本

1. 标准视图模板

2. 定时刷新物化视图（pg_cron）

十六、速查表

十七、视图 vs 物化视图 vs CTE

总结：视图使用决策树

类似文章

发表回复 取消回复

强制可更新：使用 `INSTEAD OF` 触发器

七、`WITH CHECK OPTION`（安全更新）

发表回复取消回复