九月 9, 2023
摘要：在本教程中，您将学习如何使用CREATE RECURSIVE VIEW语句创建 PostgreSQL 递归视图。
目录
PostgreSQL 递归视图简介
PostgreSQL 9.3 添加了一个新语法，用于创建 SQL 标准 中定义的递归视图。CREATE RECURSIVE VIEW语句是标准递归查询的语法糖。
下面说明了CREATE RECURSIVE VIEW语法：
CREATE RECURSIVE VIEW view_name(columns) AS
SELECT columns;
首先，在CREATE RECURSIVE VIEW子句中指定要创建的视图的名称。您可以添加可选的模式限定到视图名称中。
其次，添加 SELECT 语句以从基表中查询数据。该SELECT语句引用view_name以使视图递归。
上面的这条语句等价于下面的语句：
CREATE VIEW view_name
AS
WITH RECURSIVE cte_name (columns) AS (
SELECT ...)
SELECT columns FROM cte_name;
创建递归视图示例
我们将使用递归查询教程中创建的employees表进行演示。
以下递归查询使用公用表表达式或 CTE，返回员工及其管理者，直至 CEO 级别。
WITH RECURSIVE reporting_line AS (
SELECT
employee_id,
full_name AS subordinates
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
(
rl.subordinates || ' > ' || e.full_name
) AS subordinates
FROM
employees e
INNER JOIN reporting_line rl ON e.manager_id = rl.employee_id
) SELECT
employee_id,
subordinates
FROM
reporting_line
ORDER BY
employee_id;
employee_id | subordinates
-------------+--------------------------------------------------------------
1 | Michael North
2 | Michael North > Megan Berry
3 | Michael North > Sarah Berry
4 | Michael North > Zoe Black
5 | Michael North > Tim James
6 | Michael North > Megan Berry > Bella Tucker
7 | Michael North > Megan Berry > Ryan Metcalfe
8 | Michael North > Megan Berry > Max Mills
9 | Michael North > Megan Berry > Benjamin Glover
10 | Michael North > Sarah Berry > Carolyn Henderson
11 | Michael North > Sarah Berry > Nicola Kelly
12 | Michael North > Sarah Berry > Alexandra Climo
13 | Michael North > Sarah Berry > Dominic King
14 | Michael North > Zoe Black > Leonard Gray
15 | Michael North > Zoe Black > Eric Rampling
16 | Michael North > Megan Berry > Ryan Metcalfe > Piers Paige
17 | Michael North > Megan Berry > Ryan Metcalfe > Ryan Henderson
18 | Michael North > Megan Berry > Max Mills > Frank Tucker
19 | Michael North > Megan Berry > Max Mills > Nathan Ferguson
20 | Michael North > Megan Berry > Max Mills > Kevin Rampling
(20 rows)
您可以使用CREATE RECURSIVE VIEW语句将查询转换为递归视图，如下所示：
CREATE RECURSIVE VIEW reporting_line (employee_id, subordinates) AS
SELECT
employee_id,
full_name AS subordinates
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
(
rl.subordinates || ' > ' || e.full_name
) AS subordinates
FROM
employees e
INNER JOIN reporting_line rl ON e.manager_id = rl.employee_id;
要查看 ID 为 10 的员工的汇报行，可以直接从视图中查询：
SELECT
subordinates
FROM
reporting_line
WHERE
employee_id = 10;
subordinates
-------------------------------------------------
Michael North > Sarah Berry > Carolyn Henderson
(1 row)
在本教程中，您学习了如何基于递归查询创建 PostgreSQL 递归视图。