How to write a recursive function in PostgreSQL with an additional condition?

Question

PostgreSQL

There is a table my_table with a hierarchical structure (parent parent_col). Each entry has the attribute my_bool (True/False).
To count the number of all children for a record, say 47, that have my_bool = False, I use this query:

SELECT COUNT(*) FROM my_table WHERE parent_col = 47 and  my_bool IS FALSE

With this, everything is clear, the request is very simple. But how to make it so that it counts not only direct children, but also the number of their descendants until it comes across a record where my_bool IS TRUE.
That is, the query must be recursive + with a condition. How to do it right?

Answer 1

More or less like this:

WITH RECURSIVE temp1 ( "id","parent_col","description" ) AS (
    SELECT T1."id",T1."parent_col", T1."description"
        FROM my_table T1 WHERE T1."id" = 47
    UNION ALL
    SELECT T2."id", T2."parent_col", T2."description"
        FROM my_table T2 INNER JOIN temp1 ON( temp1."id"= T2."parent_col")
)
SELECT * FROM temp1 LIMIT 100

I didn’t understand what my_bool is at all, so add it yourself.