提要:在本教程中,您将学习如何使用 CREATE RECURSIVE VIEW 语句创建 PostgreSQL 递归视图。

PostgreSQL 递归视图简介

PostgreSQL 9.3 添加了一个新的语法来创建递归视图。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)