在循環的情況下如何限制樹遍歷？

所以我想限制樹深度遍歷（作為最簡單的遞歸殺手工具）。將規範化記錄轉換為樹的範常式式碼：

CREATE TABLE items (
 item_id     serial PRIMARY KEY,
 title text
);
CREATE TABLE joins (
 id          serial PRIMARY KEY,
 item_id     int,
 child_id    int
);
INSERT INTO items (item_id,title) VALUES
 (1,'PARENT'),
 (2,'LEVEL 2'),
 (3,'LEVEL 3.1'),
 (4,'LEVEL 4.1'),
 (5,'LEVEL 4.2'),
 (6,'LEVEL 3.2');
INSERT INTO joins (item_id, child_id) VALUES
 (1,2),
 (2,3),
 (3,4),
 (3,5),
 (2,6);

WITH RECURSIVE t(item_id, json) AS (
       SELECT item_id, to_jsonb(items)
       FROM items
       WHERE NOT EXISTS (
               SELECT 1
               FROM joins
               WHERE items.item_id = joins.item_id
       )
       UNION ALL
       SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json )
       FROM t
       JOIN joins AS j
               ON t.item_id = j.child_id
       JOIN items AS parent
               ON j.item_id = parent.item_id
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;

由於這個美麗的答案，它起作用了。現在想像我們有

INSERT INTO joins (item_id, child_id) VALUES
 (1,2),
 (2,3),
 (3,1);

所以我們得到了一個循環1->2->3->1，我想使用限制器來限制它的輸出——我希望我的所有樹都不大於我給這個樹建構子設置的深度 X。如何在 postgres 13+ 中做這樣的事情？

真的很簡單：添加遞歸級別值並對其進行過濾：

WITH RECURSIVE t(item_id, json, level) AS (
       SELECT item_id, to_jsonb(items), 1
       FROM items
       WHERE NOT EXISTS (
               SELECT 1
               FROM joins
               WHERE items.item_id = joins.item_id
       )
       UNION ALL
       SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json ),
              level + 1
       FROM t
       JOIN joins AS j
               ON t.item_id = j.child_id
       JOIN items AS parent
               ON j.item_id = parent.item_id
       WHERE level < 3
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;

引用自：https://dba.stackexchange.com/questions/292563