Форматирование иерархии

Мы привыкли видеть иерархичные данные в виде списков, в которых вложенность и определение родительского элемента определяется отступом слева. Посмотри, например, на списки в любимом текстовом редакторе. Или на вложенные блоки кода.

Давай отформатируем каталог товаров. Все данные для этого у нас есть. Уровень вложенности знаем, сортировать строки в нужном порядке умеем...

Нам осталось сделать мелочь - добавить пробелов слева от названия.

Добавим точек, для наглядности. Алгоритм следующий:

• Если каталог родительский (level = 1), то ничего добавлять не нужно.
• Для вложенных каталогов будем добавлять по четыре точки на каждый уровень вложенности.

Воспользуемся функцией [lpad] (https://learndb.ru/articles/article/70). Будем добивать пустую строку до нужной длины.

Длина строки равна (level - 1) * 4.

SELECT lpad('', 4 * (1-1), '.') AS level_1,
       lpad('', 4 * (2-1), '.') AS level_2,
       lpad('', 4 * (3-1), '.') AS level_3,
       lpad('', 4 * (4-1), '.') AS level_4

К нужному количеству точек осталось добавить название каталога справа.

Собрав все вместе, получаем:

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) * 8) || c.name AS name,
       c.level
  FROM lv_hierarchy c
 ORDER BY path_sort