PostgreSQL视图不存数据？那它怎么简化查询还能递归生成序列和控制权限？

kknone
166 阅读16分钟

一、什么是PostgreSQL视图？

视图（View）是基于SQL查询结果的虚拟表——它不物理存储数据，仅保存查询的逻辑定义。当你查询视图时，PostgreSQL会动态执行视图的定义查询，返回基础表的最新结果。视图的核心价值在于：

  • 简化复杂查询：将常用的多表关联、过滤逻辑封装成视图，避免重复写冗长SQL；
  • 限制数据访问：仅暴露基础表的部分列/行给用户，保障数据安全；
  • 隔离 schema 变化：当基础表结构调整时，只需修改视图定义，不影响应用代码。

例如，若你频繁需要查询“喜剧类型的电影”，可以创建comedies视图，之后直接查询comedies即可，无需每次写WHERE kind = 'Comedy'。

二、创建视图的基本语法

PostgreSQL用CREATE VIEW语句创建视图，完整语法如下（来自官方文档）：

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

下面拆解关键参数的含义和用法：

1. 可选修饰符：OR REPLACE、TEMPORARY

  • OR REPLACE：若同名视图已存在，则替换它（需保证新视图的列名、顺序、类型与原视图一致，可添加新列到末尾）；
  • TEMPORARY（或TEMP）：创建临时视图，会话结束后自动删除。若视图引用的表是临时表，视图会自动转为临时视图。

示例：替换已有视图并添加新列：

-- 原视图：仅包含id、title
CREATE VIEW comedies AS SELECT id, title FROM films WHERE kind = 'Comedy';

-- 替换视图：添加release_year列
CREATE OR REPLACE VIEW comedies AS 
SELECT id, title, release_year FROM films WHERE kind = 'Comedy';

2. RECURSIVE：递归视图

递归视图用于处理递归结构（如层级数据、序列生成），等价于WITH RECURSIVE的CTE（公共表表达式）。必须显式指定列名列表。

递归视图的结构分为两部分：

  • 初始查询（非递归部分）：返回递归的起始行；
  • 递归查询（递归部分）：引用视图本身，返回下一层行，直到条件不满足。

示例：生成1到100的连续数字：

CREATE RECURSIVE VIEW nums_1_100 (n) AS
VALUES (1)  -- 初始查询：起始值1
UNION ALL
SELECT n + 1 FROM nums_1_100 WHERE n < 100;  -- 递归查询：每次加1，直到n=99

查询结果：SELECT * FROM nums_1_100;（返回1~100的整数）

3. 列名指定

若不指定column_name，视图列名将从查询中自动推导（如SELECT id, title的列名是id、title）。但建议显式指定，避免默认的?column?或歧义。

示例：显式指定列名：

CREATE VIEW film_ratings (film_id, average_rating) AS
SELECT film_id, AVG(rating) FROM user_ratings GROUP BY film_id;

4. WITH选项：视图的高级参数

WITH clause 用于设置视图的附加属性，常见参数：

  • security_barrier（布尔值）：用于行级安全（RLS），确保视图的WHERE条件先于用户的查询条件执行，防止信息泄露；
  • security_invoker（布尔值）：默认false。若设为true，访问基础表的权限检查将使用执行查询的用户（而非视图所有者）的权限；
  • check_option（枚举值）：等价于后面的CHECK OPTION（local或cascaded）。

示例：创建安全屏障视图（用于RLS）：

CREATE VIEW secure_films WITH (security_barrier = true) AS
SELECT * FROM films WHERE is_public = true;

5. AS query：视图的核心逻辑

query是视图的定义查询，必须是有效的SELECT或VALUES语句。例如：

-- 查询“2020年以后上映的喜剧电影”
CREATE VIEW recent_comedies AS
SELECT id, title, release_year, director 
FROM films 
WHERE kind = 'Comedy' AND release_year >= 2020;

6. CHECK OPTION：确保更新的行可见

CHECK OPTION用于可更新视图，确保INSERT/UPDATE/MERGE操作产生的行仍满足视图的定义条件（即能通过视图看到）。有两种模式：

  • LOCAL：仅检查当前视图的条件，不检查基础视图；
  • CASCADED：检查当前视图和所有基础视图的条件（默认）。

示例：

-- 基础视图：喜剧电影
CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy';

-- 子视图：U级喜剧（LOCAL CHECK OPTION）
CREATE VIEW universal_comedies AS
SELECT * FROM comedies WHERE classification = 'U'
WITH LOCAL CHECK OPTION;  -- 仅检查classification='U'

-- 子视图：PG级喜剧（CASCADED CHECK OPTION）
CREATE VIEW pg_comedies AS
SELECT * FROM comedies WHERE classification = 'PG'
WITH CASCADED CHECK OPTION;  -- 检查classification='PG' + kind='Comedy'

三、可更新视图（Updatable Views）

不是所有视图都能执行INSERT/UPDATE/DELETE/MERGE——PostgreSQL会自动判断视图是否“可更新”。

1. 自动可更新的条件

视图需满足以下所有条件：

  • FROM子句仅包含一个表或另一个可更新视图；
  • 视图定义无WITH/DISTINCT/GROUP BY/HAVING/LIMIT/OFFSET；
  • 无顶层集合操作（UNION/INTERSECT/EXCEPT）；
  • SELECT列表无聚合函数（AVG/SUM）、窗口函数（ROW_NUMBER）或返回集合的函数（generate_series）。

2. 可更新列 vs 只读列

  • 可更新列：直接引用基础表的可更新列（如films.id）；
  • 只读列：计算列（函数结果）、聚合结果、子查询结果等。

示例：混合列的视图：

CREATE VIEW comedy_details AS
SELECT 
    f.id,  -- 可更新（来自films.id）
    f.title,  -- 可更新（来自films.title）
    country_code_to_name(f.country_code) AS country,  -- 只读（函数结果）
    (SELECT AVG(r.rating) FROM user_ratings r WHERE r.film_id = f.id) AS avg_rating  -- 只读（聚合结果）
FROM films f
WHERE f.kind = 'Comedy';

若尝试更新country列，会报错：ERROR: column "country" is read only。

3. 不可更新视图的解决方案

若视图不满足自动可更新条件，可通过**INSTEAD OF触发器**实现更新——将视图的操作转换为基础表的操作。

示例：为聚合视图添加更新触发器：

-- 不可更新视图（有GROUP BY）
CREATE VIEW film_ratings AS
SELECT film_id, AVG(rating) AS avg_rating FROM user_ratings GROUP BY film_id;

-- 创建触发器函数：将视图更新转为基础表更新
CREATE OR REPLACE FUNCTION update_film_rating()
RETURNS TRIGGER AS $$
BEGIN
    -- 简化逻辑：将avg_rating更新到user_ratings表的对应film_id
    UPDATE user_ratings SET rating = NEW.avg_rating WHERE film_id = NEW.film_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定INSTEAD OF触发器到视图
CREATE TRIGGER trigger_update_film_rating
INSTEAD OF UPDATE ON film_ratings
FOR EACH ROW EXECUTE FUNCTION update_film_rating();

四、递归视图的高级应用

递归视图常用于处理层级结构或序列生成，以下是两个典型场景：

1. 场景1：生成日期序列

需求：生成2024年1月的所有日期：

CREATE RECURSIVE VIEW jan_2024_dates (date) AS
VALUES ('2024-01-01'::date)  -- 初始日期
UNION ALL
SELECT date + INTERVAL '1 day' FROM jan_2024_dates WHERE date < '2024-01-31';  -- 每天加1天

查询结果：SELECT * FROM jan_2024_dates;（返回31行日期）

2. 场景2：查询组织层级

假设employees表有id（员工ID）、name（姓名）、manager_id（上级ID），需求：查询所有员工的层级关系：

CREATE RECURSIVE VIEW employee_hierarchy (id, name, manager_id, level) AS
-- 初始查询：顶层管理者（无上级）
SELECT id, name, manager_id, 1 FROM employees WHERE manager_id IS NULL
UNION ALL
-- 递归查询：关联上级，层级+1
SELECT e.id, e.name, e.manager_id, eh.level + 1 
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id;

查询结果：SELECT * FROM employee_hierarchy;（返回所有员工的层级，如顶层管理者level=1，下属level=2）

五、视图的安全与权限

PostgreSQL视图的权限默认基于视图所有者：用户访问视图时，PostgreSQL检查视图所有者对基础表的权限，而非用户自己的权限。

1. security_invoker：以调用者权限访问基础表

若视图的security_invoker设为true，则权限检查会使用执行查询的用户的权限。例如：

-- 视图：仅显示当前用户的电影
CREATE VIEW user_films WITH (security_invoker = true) AS
SELECT * FROM films WHERE user_id = current_user;

当用户alice查询user_films时，PostgreSQL会检查alice对films表的权限，而非视图所有者的权限。

2. security_barrier：防止信息泄露

security_barrier用于行级安全（RLS），确保视图的WHERE条件先于用户的查询条件执行，避免“条件泄露”。例如：

-- 视图：仅显示公开电影
CREATE VIEW secure_films WITH (security_barrier = true) AS
SELECT * FROM films WHERE is_public = true;

当用户查询secure_films WHERE title LIKE '%secret%'时，PostgreSQL会先过滤is_public = true的电影，再执行用户的条件，确保不泄露非公开电影的信息。

六、课后Quiz

问题1：如何确保插入到视图的行满足所有基础视图的条件？

答案：使用WITH CASCADED CHECK OPTION。例如：

CREATE VIEW pg_comedies AS
SELECT * FROM comedies WHERE classification = 'PG'
WITH CASCADED CHECK OPTION;

插入时会检查comedies视图的kind = 'Comedy'和当前视图的classification = 'PG'。

问题2：请写出创建“1到50的数字”的递归视图的SQL。

答案：

CREATE RECURSIVE VIEW nums_1_50 (n) AS
VALUES (1)  -- 初始值
UNION ALL
SELECT n + 1 FROM nums_1_50 WHERE n < 50;  -- 递归加1

问题3：为什么聚合视图无法自动更新？如何解决？

答案：聚合视图包含GROUP BY或聚合函数（如AVG），不满足自动可更新的条件。解决方法是为视图创建INSTEAD OF触发器，将更新操作转换为基础表的操作。

七、常见报错及解决方案

报错1：ERROR: cannot create view without a query

原因：创建视图时缺少AS query，例如：

CREATE VIEW comedies;  -- 错误：无查询逻辑

解决：添加AS和有效查询：

CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy';

报错2：ERROR: view "view_name" cannot be modified because it contains a non-updatable column

原因：尝试更新视图中的只读列（如聚合结果），例如：

-- 视图有avg_rating（聚合列，只读）
CREATE VIEW film_ratings AS SELECT film_id, AVG(rating) AS avg_rating FROM user_ratings GROUP BY film_id;

-- 错误：更新只读列
UPDATE film_ratings SET avg_rating = 4 WHERE film_id = 1;

解决：

  1. 去掉视图中的只读列，使其成为自动可更新视图；
  2. 为视图创建INSTEAD OF触发器。

报错3：ERROR: recursive view "view_name" must have a column list

原因：创建递归视图时未指定列名列表，例如：

-- 错误：无列名列表
CREATE RECURSIVE VIEW nums_1_100 AS VALUES (1) UNION ALL SELECT n+1 FROM nums_1_100 WHERE n < 100;

解决：显式指定列名列表：

CREATE RECURSIVE VIEW nums_1_100 (n) AS VALUES (1) UNION ALL SELECT n+1 FROM nums_1_100 WHERE n < 100;

报错4：ERROR: permission denied for table base_table

原因：视图所有者无基础表的权限，例如：

-- 视图所有者view_owner无films表的SELECT权限
CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy';

解决：授予视图所有者基础表的权限：

GRANT SELECT ON films TO view_owner;

若视图的security_invoker = true，则需授予执行查询的用户基础表的权限。

八、参考链接

参考链接：www.postgresql.org/docs/17/sql…

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长，阅读完整的文章：PostgreSQL视图不存数据？那它怎么简化查询还能递归生成序列和控制权限？

往期文章归档
免费好用的热门在线工具
avatar
文章
阅读
粉丝