PostgreSQL Recursive CTE - Return Empty if false value found-CodePudding

Using a recursive query, I'm querying for all ancestors of a field using a parent id field. If a false value is found in the enabled field for any ancestor, then the query should return empty rows.

Say I have the following table called my_table

Field	Type
id	integer
parent	integer
enabled	boolean

It's populated with the following data:

id	parent	enabled
1	null	true
2	1	true
3	1	true
4	2	true

I have the following recursive query:

WITH recursive my_rec AS
(
    SELECT id, parent, enabled
    FROM my_table
    WHERE id = 4
    
    UNION ALL

    SELECT t.id, t.parent, t.enabled
    FROM my_table t
    INNER JOIN my_rec
    ON t.id = my_rec.parent
)

SELECT *
FROM my_rec

For id = 4 this correctly returns

id	parent	enabled
4	2	true
2	1	true
1	null	true

But say I were to update id 2 so that it's changed from enabled true to false. Example:

id	parent	enabled
1	null	true
2	1	false
3	1	true
4	2	true

Now I want my query to return for id = 4 nothing. This is because in the "ancestry" tree of id 4, enabled is false for id 2.

How can I update my query above to achieve this?

CodePudding user response：

If I understand correctly you can try to use NOT EXISTS subquery to judge cte if any enabled is false didn't show anything as your expect, otherwise show all of them.

WITH recursive my_rec AS
(
    SELECT id, parent, enabled
    FROM my_table
    WHERE id = 4
    UNION ALL
    SELECT t.id, t.parent, t.enabled
    FROM my_table t
    INNER JOIN my_rec
    ON t.id = my_rec.parent
)
SELECT *
FROM my_rec
WHERE NOT EXISTS (
   SELECT 1
   FROM my_rec
   WHERE enabled = false
)

or we can use COUNT condition aggregate function to make it, getting false_cnt and judgement.

WITH recursive my_rec AS
(
    SELECT id, parent, enabled
    FROM my_table
    WHERE id = 4
    UNION ALL
    SELECT t.id, t.parent, t.enabled
    FROM my_table t
    INNER JOIN my_rec
    ON t.id = my_rec.parent
)
SELECT *
FROM (
 SELECT *,COUNT(*) FILTER(WHERE enabled = false) OVER() false_cnt
 FROM my_rec
) t1
WHERE false_cnt = 0

sqlfiddle