Нумерация вложенных списков

Любое оглавление выглядит примерно так:

1. ...
   1.1. ...
   1.2. ...
   1.2. ...
2. ...
   2.1. ...
   2.2. ...
      2.2.1. ...
      2.2.2. ...
      2.2.3. ...
   2.3. ...

Научимся строить такое оглавление с помощью SQL запроса.

Взглянем внимательно на массив, по которому мы сортируем иерархию каталогов.

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 *
 FROM lv_hierarchy
ORDER BY path_sort

#	category_id	parent_category_id	name	level	path	path_sort
1	1	NULL	Товары для дома	1	/Товары для дома	["1"]
2	3	1	Бытовая техника	2	/Товары для дома/Бытовая техника	["1","1"]
3	2	NULL	Цифровая техника	1	/Цифровая техника	["2"]
4	7	2	Аудиотехника	2	/Цифровая техника/Аудиотехника	["2","1"]
5	6	2	Игровые консоли	2	/Цифровая техника/Игровые консоли	["2","2"]
6	4	2	Ноутбуки и аксессуары	2	/Цифровая техника/Ноутбуки и аксессуары	["2","3"]
7	9	4	Ноутбуки	3	/Цифровая техника/Ноутбуки и аксессуары/Ноутбуки	["2","3","1"]
8	10	4	Рюкзаки	3	/Цифровая техника/Ноутбуки и аксессуары/Рюкзаки	["2","3","2"]
9	8	2	Сотовые телефоны	2	/Цифровая техника/Сотовые телефоны	["2","4"]
10	5	2	Фотоаппараты	2	/Цифровая техника/Фотоаппараты	["2","5"]

Числа в массивах именно те, которые нам нужны для нумерации.

Осталось собрать все в строку. Можно конкатенировать номер строки в рамках общего родителя на каждой итерации иерархического запроса. Но мы воспользуемся встроенной функцией array_to_string для path_sort:

array_to_string(массив, разделитель)

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
  FROM lv_hierarchy c
 ORDER BY path_sort

#	category_id	full_name
1	1	1. Товары для дома
2	3	....1.1. Бытовая техника
3	2	2. Цифровая техника
4	7	....2.1. Аудиотехника
5	6	....2.2. Игровые консоли
6	4	....2.3. Ноутбуки и аксессуары
7	9	........2.3.1. Ноутбуки
8	10	........2.3.2. Рюкзаки
9	8	....2.4. Сотовые телефоны
10	5	....2.5. Фотоаппараты