PostgreSQL 递归视图

摘要：在本教程中，您将学习如何使用 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 递归视图。

PostgreSQL 递归视图介绍

创建递归视图示例

本页目录