Пример работы с рекурсивными запросами в PostgreSQL (8.4)

Пусть требуется выяснить уровни иерархии, которые задаются в виде иерархической таблицы. Разберем на примере.

Создаем таблицу:

create table test_rec (
  id int,
  pid int,
  descr varchar(256)
);

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

insert into test_rec values (1,NULL, 'Россия');
insert into test_rec values (2,1, 'Москва');
insert into test_rec values (3,2, 'Головной');
insert into test_rec values (4,1, 'Приморье');
insert into test_rec values (5,4, 'Офис 1');
insert into test_rec values (6,4, 'Офис 2');

Делаем рекурсивный обход:

WITH RECURSIVE  Rec  AS (
   SELECT id, descr, 0 as levels FROM test_rec where pid is null
   UNION all
   SELECT  test_rec.id, test_rec.descr, levels + 1
   FROM Rec inner join  test_rec on Rec.id = test_rec.pid /*отношение*/
   )
 SELECT * FROM Rec;

Понимание запроса:

1. WITH RECURSIVE говорит о том, что работаем с рекурсией.
2. Rec  AS - создаем временную таблицу.
3. SELECT id, title, 0 as levels FROM test_table where pid is null показывает, с какой записи начинается чтение таблицы (запись с пустым предком).
4. UNION all SELECT  test_table.id, test_table.title, levels + 1  FROM Rec inner join  test_table on Rec.id = test_table.pid - сам рекурсивный обход. Обращаем внимание на используемые имена таблиц в запросе. К переменной levels добавляем 1.  inner join задает соотношение один к одному.
5. SELECT * FROM Rec - вывод результата.

С иерархическими списками я столкнулся при работе со справочниками в 1с. Пример иерархического списка - справочник ТМЦ и т.д. Думаю, что это - не первая и не последняя система с иерархической структурой справочников.

Ссылки на материалы:

1. Пример основан на: http://habrahabr.ru/post/43955/, является полной переработкой и заточен под мои цели. Очень жаль, что у автора нет времени писать работающие запросы.
2. Рекомендую глянуть. Запросы тоже не рабочие, но нет ошибок в логике запроса: http://habrahabr.ru/post/27439/.
3. Документация, которая пригодилась для понимания: http://www.postgresql.org/docs/current/static/queries-with.html.
4. Описание структуры таблиц на 1 С7: http://www.script-coding.com/v77tables.html.
5. Работа с рекурсивными запросами: http://developer-remarks.blogspot.ru/2012/12/hierarchy-postgresql.html

• Вконтакте ()
• Google+
• Facebook ( )

• < Назад
• Вперёд >