Как написать рекурсивную функцию на PostgreSQL с дополнительным условием?

Question

Есть таблица my_table с иерархической структурой (родитель parent_col). У каждой записи есть признак my_bool (True/False). Чтобы подсчитать количество всех детей для записи, к примеру, 47, у которых значение my_bool = False, я использую такой запрос:

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

С этим всё понятно, запрос очень простой. Но каким образом сделать так, чтобы считало не только прямых детей, но и количество их потомков до тех пор, пока не наткнется на запись, где my_bool IS TRUE. Т.е., запрос должен быть рекурсивным + с условием. Можете подсказать, пожалуйста, как это правильно сделать? У меня знаний SQL для этого не хватает. Спасибо

Answer 1

WITH RECURSIVE cte (id) AS
( SELECT id                     -- выбрать детей "1 уровня"
      FROM my_table 
      WHERE parent_col = 47 
        AND my_bool IS FALSE
UNION ALL
  SELECT my_table.id            -- выбрать детей "следующего" уровня
      FROM my_table 
      JOIN cte ON my_table.parent_col = cte.id 
      WHERE my_table.my_bool IS FALSE 
)
SELECT COUNT(*)
FROM cte 
LIMIT 100                       -- на всякий случай - а вдруг имеются "петли"?
;

БЛОГ НА HUSL

Как написать рекурсивную функцию на PostgreSQL с дополнительным условием?

Ответы (1 шт):