PostgreSQL 教程: 递归视图

九月 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 递归视图。