Рекурсивный запрос на postgres

5 мая 2018

Рассмотрим составление рекурсивных запросов на PostgreSQL для иерархических данных на примере следующей таблицы:

create table hierarchy_example (
    id serial not null,
    name character varying(100),
    parent_id integer,
    constraint id_pk primary key (id)
)

Здесь поле parent_id содержит номер записи, которая является родительской по отношению к данной. Если parent_id = null, считаем, что это - корневой элемент иерархии.

Заполним таблицу данными:

insert into hierarchy_example (name, parent_id) values ('root', null);
insert into hierarchy_example (name, parent_id) values ('item1', 1);
insert into hierarchy_example (name, parent_id) values ('item2', 1);
insert into hierarchy_example (name, parent_id) values ('subitem1', 2);

Теперь составим запрос для прохода по этой иерархии, от элемента с именем subitem1 до root. На каждой итерации будем добавлять новую строку во временную таблицу temp1.

with recursive temp1 (id, parent_id, name, path) as (
select t1.id, t1.parent_id, t1.name, cast (t1.name as varchar (50)) as path
from hierarchy_example t1 where t1.name = 'subitem1'
union
select t2.id, t2.parent_id, t2.name, cast (temp1.path || '->'|| t2.name as varchar(50))
from hierarchy_example t2 inner join temp1 on (temp1.parent_id = t2.id))
select * from 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

Рекурсивный запрос на postgres

Облако тэгов

Последние статьи