Stop recursion in postgres as soon as the condition is met-CodePudding

I have this table of a hierarchy

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

I want to traverse from iphone and as soon as I encounter row with 'rule' not NULL, I want to select that row and finish recursion then and there only, I am using this query,

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 ;

giving the result,

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

What I want here is as soon as the row with rule column not NULL found, it should return that row and finish the recursion, In above example it should return the second row with item 'touch_screen', But it is printing the 3rd row also.

How can I modify this query to achieve this goal

CodePudding user response：

You want to stop recursion when the current node's rule is not null and its child's rule is null:

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
        AND items.rule IS NULL
)
SELECT * FROM items;