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

Актуальная версия этой статьи на моём новом сайте devmark.ru

Рассмотрим составление рекурсивных запросов на 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, считаем, что это - корневой элемент иерархии.

Теперь составим запрос для прохода по этой иерархии, от элемента с именем 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.id = 4
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, в которую мы помещали строки в каждой итерации.

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

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