Статьи
Утилиты Telegram YouTube Отзывы

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

Видеогайд

21 ноября 2021

Тэги: PostgreSQL, SQL, YouTube.

Когда различные иерархии находятся в пределах одной таблицы, может потребоваться пройтись по одной из веток этой иерархии, начиная с самого нижнего элемента и заканчивая корневым. Примером иерархических данных может служить структура каталогов на файловой системе, оргструктура в компании или справочник географических объектов (города-страны-континенты).

PostgreSQL предоставляет специальный синтаксис with recursive для написания рекурсивных запросов. Эти запросы позволяют легко выбирать иерархические данные.

Данный гайд также доступен в формате видео на YouTube.

Рассмотрим конкретную таблицу:

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 – он не входит в данную ветвь иерархии.



Комментарии

Добавить комментарий

×

devmark.ru