Листовые строки CONNECT_BY_ISLEAF

Строку в рекурсивном запросе называют листом, если в рекурсивной части подзапроса для нее не найдено дочерних строк.

В Oracle в рекурсивных запросах через CONNECT BY есть элегантный способ определения листовых строк - псевдостолбец CONNECT_BY_ISLEAF. Он равен 1 для листовых строк, и 0 для родительских.

К сожалению, Postgres нам такой роскоши не предоставляет. Поэтому придется что-то придумывать самим.

Возьмем иерархию категорий товаров

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       c.level
  FROM lv_hierarchy c
 ORDER BY path_sort

Способ 1. Подзапрос not exists

Какие категории являются листьями? Те, у которых нет подкатегорий.

Определить категории, в которых нет подкатегорий, можно с помощью подзапроса not exists:

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       not exists (
         SELECT 1
           FROM category ch
          WHERE ch.parent_category_id = c.category_id
       ) AS is_leaf
  FROM lv_hierarchy c
 ORDER BY path_sort

Способ 2. Сравнить уровень вложенности текущей и следующей строки.

Посмотри внимательно на уровень вложенности в отсортированных строках иерархии.

Если у текущего каталога есть вложенные подкаталоги, то на следующей строке будет располагаться один из них. Уровень вложенности подкаталога будет больше уровня вложенности текущего каталога на 1.

Получить значение поля в следущей строке можно с помощью аналитической функции lead.

lead(value, shift, default_value) over (order by sorting)

где:

• value - какое значение взять, ссылается на следующую строку.
• shift - через сколько строк смотреть.
• default_value - какое значение использовать, если следующей строки нет. Ссылается на текущую строку.
• sorting - в каком порядке расположить строки для вычисления значения.

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       c.level,
       lead(c.level, 1, c.level) over (order by c.path_sort) AS level_next
  FROM lv_hierarchy c
 ORDER BY path_sort

Осталось сравнить level и level_next:

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   FROM category c
  WHERE c.parent_category_id IS NULL

  UNION ALL

 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        p.level + 1 AS level,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name,
       c.level >= lead(c.level, 1, c.level) over (order by c.path_sort) AS is_leaf
  FROM lv_hierarchy c
 ORDER BY path_sort

P.S. Зачем вообще решать одну задачу разными способами? Когда в таблицах очень много строк, тогда запросы могут выполняться медленно. В зависимости от плана выполнения запроса время выполнения может различаться во много раз. Чтобы запрос выполнялся по разному, нужно уметь получать один и тот же результат разными способами.