Как получить потомков в таблице с parent_case_id

Условие

В PostgreSQL таблица cases(case_id PRIMARY KEY, parent_case_id REFERENCES cases). Найдите узел $1 и всех его потомков. Учтите, что внешний ключ сам по себе не запрещает циклы.

Ответ

Используйте рекурсивный CTE. Для выбранных полей UNION удаляет повторные строки и не даёт одному циклу бесконечно добавлять те же узлы.

Пример:

WITH RECURSIVE tree AS (
  SELECT case_id, parent_case_id FROM cases WHERE case_id = $1
  UNION
  SELECT c.case_id, c.parent_case_id
  FROM cases c JOIN tree t ON c.parent_case_id = t.case_id
)
SELECT * FROM tree;

Индекс parent_case_id полезен для поиска детей. Если добавлять depth или path, строки на цикле уже различаются: потребуется явное отслеживание посещённых узлов.