PostgreSQL递归查询：满足条件时立即终止递归

场景与数据 #

现有层级结构表product_categories，数据如下：

select * from product_categories;
 id | parent_id |     item     |     rule
----+-----------+--------------+---------------
  1 |           | ecomm        |
  2 |         1 | grocceries   |
  3 |         1 | electronics  |
  5 |         3 | TV           |
  6 |         4 | touch_screen | Rules applied
  7 |         4 | qwerty       |
  8 |         6 | iphone       |
  4 |         3 | mobile       | mobile rules

需求 #

从item为'iphone'的节点向上遍历层级，一旦遇到rule列非空的行，就返回该行并立即终止递归，不再继续向上查找父节点。

原查询及问题 #

原查询代码：

WITH RECURSIVE items AS (
        SELECT id, item, parent_id, rule
        FROM product_categories
        WHERE item = 'iphone'
    UNION ALL
        SELECT p.id, p.item, p.parent_id, p.rule
        FROM product_categories p
        JOIN items ON p.id = items.parent_id
        WHERE p.rule is not NULL
)
SELECT * FROM items ;

查询结果：

id |     item     | parent_id |     rule
----+--------------+-----------+---------------
  8 | iphone       |         6 |
  6 | touch_screen |         4 | Rules applied
  4 | mobile       |         3 | mobile rules

原查询的问题是：会返回所有符合rule非空的父节点，无法在找到第一个匹配行后终止递归。

修改后的查询方案 #

方法一：通过递归条件直接控制终止 #

WITH RECURSIVE items AS (
    SELECT id, item, parent_id, rule
    FROM product_categories
    WHERE item = 'iphone'
    UNION ALL
    SELECT p.id, p.item, p.parent_id, p.rule
    FROM product_categories p
    JOIN items ON p.id = items.parent_id
    WHERE items.rule IS NULL  -- 仅当前节点无rule时，才继续向上遍历父节点
)
SELECT id, item, parent_id, rule
FROM items
WHERE rule IS NOT NULL
LIMIT 1;

方法二：增加深度标记确保取首个匹配行（更严谨） #

如果担心递归顺序不确定，可以添加深度字段明确遍历顺序：

WITH RECURSIVE items AS (
    SELECT id, item, parent_id, rule, 1 AS depth
    FROM product_categories
    WHERE item = 'iphone'
    UNION ALL
    SELECT p.id, p.item, p.parent_id, p.rule, items.depth + 1 AS depth
    FROM product_categories p
    JOIN items ON p.id = items.parent_id
    WHERE items.rule IS NULL
)
SELECT id, item, parent_id, rule
FROM items
WHERE rule IS NOT NULL
ORDER BY depth ASC
LIMIT 1;

结果说明 #

执行修改后的查询，会返回首个匹配的行：

id |     item     | parent_id |     rule
----+--------------+-----------+---------------
  6 | touch_screen |         4 | Rules applied

核心逻辑 #

1. items.rule IS NULL作为递归连接的条件：确保只有当前递归到的节点没有非空rule时，才会继续向上查找父节点。一旦找到有非空rule的节点，后续递归就会停止。
2. 最后筛选出rule非空的行，并取第一行（或按深度从小到大取第一行），得到需要的首个符合条件的节点。

内容的提问来源于stack exchange，提问作者Rahul