pgsql递归处理树形结构数据

PostgreSQL 支持递归查询，可以使用 WITH RECURSIVE 语句来处理树形结构的数据

首先，创建一个表来存储树形结构的数据：

CREATE TABLE tree (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES tree(id)
);

接下来，向表中插入一些示例数据：

INSERT INTO tree (name, parent_id) VALUES ('root', NULL);
INSERT INTO tree (name, parent_id) VALUES ('child1', 1);
INSERT INTO tree (name, parent_id) VALUES ('child2', 1);
INSERT INTO tree (name, parent_id) VALUES ('grandchild1', 2);
INSERT INTO tree (name, parent_id) VALUES ('grandchild2', 2);

现在，我们可以使用递归查询来获取树形结构的数据。以下是一个查询示例，它将返回树形结构的所有节点及其父节点：

WITH RECURSIVE tree_hierarchy AS (
    SELECT id, name, parent_id, ARRAY[id] AS path
    FROM tree
    WHERE parent_id IS NULL

    UNION ALL

    SELECT t.id, t.name, t.parent_id, th.path || t.id
    FROM tree t
    JOIN tree_hierarchy th ON t.parent_id = th.id
)
SELECT * FROM tree_hierarchy;

这个查询首先从根节点（parent_id IS NULL）开始，然后递归地添加子节点。ARRAY[id] 用于存储从根节点到当前节点的路径。

运行此查询后，你将看到类似以下的输出：

 id |   name   | parent_id |   path
----+----------+-----------+-----------
  1 | root     |          | {1}
  2 | child1   |         1 | {1,2}
  3 | child2   |         1 | {1,3}
  4 | grandchild1 |       2 | {1,2,4}
  5 | grandchild2 |       2 | {1,2,5}

这样，你就可以使用 PostgreSQL 的递归查询功能来处理树形结构的数据了。