4 мая 2018
Тэги: PostgreSQL
Рассмотрим составление рекурсивных запросов на PostgreSQL для иерархических данных на примере следующей таблицы:
Здесь поле parent_id содержит номер записи, которая является родительской по отношению к данной. Если parent_id = null, считаем, что это - корневой элемент иерархии.
Заполним таблицу данными:
Теперь составим запрос для прохода по этой иерархии, от элемента с именем subitem1 до root. На каждой итерации будем добавлять новую строку во временную таблицу temp1.
Рекурсивный запрос начинается с ключевых слов with recursive. Далее следует именованный набор полей временной таблицы temp1, в которую мы будем добавлять данные на каждой итерации.
Внутри рекурсивный запрос (то, что записано в скобках после ключевого слова as) можно разделить на две части, которые объединены ключевым словом union. Первая часть - это запрос для поиска элемента, с которого следует начать рекурсивный запрос. Вторая часть - то, что выполняется в каждой итерации. Здесь мы выбираем номер элемента, номер его родительского элемента, а также для наглядности определяем временную переменную path, в которой будет содержаться пройденный путь по иерархии.
В самом конце следует обычный запрос, который выполняется к временной таблице temp1, в которую мы помещали строки в каждой итерации.
Результат выполнения запроса:
id | parent_id | name | path |
4 | 2 | subitem1 | subitem1 |
2 | 1 | item1 | subitem1->item1 |
1 | root | subitem1->item1->root |
Как видим, в столбце path последовательно отображается путь от subitem1 до root. Также обратите внимание, что в столбце name нет элемента item2 - он не входит в данную ветвь иерархии.
Kotlin, Java, Java 11, Java 10, Java 9, Java 8, Spring, Spring Boot, Spring Data, SQL, PostgreSQL, Oracle, Hibernate, Collections, Stream API, многопоточность, Apache, maven, gradle, JUnit, ООП, алгоритмы, головоломки, rest