posgresql 递归查询

在 PostgreSQL 中，递归查询用于处理树形结构的数据，例如在组织架构或分类系统中。这里有两个关键点：

1. **查找子节点**[^1]:

   WITH RECURSIVE tree AS (
       SELECT id, parent_id, name, ARRAY[]::integer[] AS path
       FROM your_table
       WHERE parent_id IS NULL
       UNION ALL
       SELECT child.id, child.parent_id, child.name, array_append(tree.path, child.id)
       FROM your_table AS child
       JOIN tree ON child.parent_id = tree.id
   )
   SELECT * FROM tree;

这个查询首先选择根节点（parent_id IS NULL），然后递归地加入每个节点及其子节点到 `path` 列。

2. **查找父节点**：
可以使用反向查询找到某个节点的所有祖先：

   WITH RECURSIVE ancestors AS (
       SELECT id, parent_id, name, ARRAY[id]::integer[] AS path
       FROM your_table
       WHERE id = :target_node_id
       UNION ALL
       SELECT child.id, child.parent_id, child.name, array_prepend(ancestors.path, child.id)
       FROM your_table AS child
       JOIN ancestors ON ancestors.id = child.parent_id
   )
   SELECT * FROM ancestors;

其中 `:target_node_id` 是你要查找的节点的ID。