Рекурсивный запрос на 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, в которую мы помещали строки в каждой итерации.

Результат выполнения запроса:

idparent_idnamepath
42subitem1subitem1
21item1subitem1->item1
1rootsubitem1->item1->root

Как видим, в столбце path последовательно отображается путь от subitem1 до root. Также обратите внимание, что в столбце name нет элемента item2 - он не входит в данную ветвь иерархии.

Тэги: PostgreSQL.