Деревянные запросы в MIS3

Общие сведения

В postgresql есть известно три способа работы с деревьями:

1. id, parent_id и использование конструкции WITH RECURSIVE
2. Использование расширения ltree
3. Использование расширения tablefunc

Использование конструкции WITH RECURSIVE

-- Выбор корневой ветки из дерева подраздеделинй
WITH RECURSIVE dept_tree(id, name, parent_id, depth, path,
       ppath, root_id) AS (
                   SELECT id,
                          name,
                          parent_id,
                          1,
                          ARRAY [t.id],
                          t.name::text,
                          id as root_id
                   FROM mm.dept_dir t
                   WHERE t.parent_id is null
                   UNION ALL
                   SELECT s.id,
                          s.name,
                          s.parent_id,
                          tt.depth + 1,
                          path || s.id,
                          ppath || ' \ ' || s.name,
                          tt.root_id
                          
                   FROM mm.dept_dir s,
                        dept_tree tt
                   WHERE s.parent_id = tt.id)
                   
select ddd.root_id from   dept_tree  ddd  where ddd.id=6

-- Справочник травм
WITH RECURSIVE task_tree (id, name, parent_id, depth, path, ppath) AS (
  SELECT id, name, parent_id, 1, ARRAY[t.id], t.name :: text 
    FROM mm.travm t WHERE t.parent_id is null
  UNION ALL
  SELECT s.id, s.name, s.parent_id, tt.depth + 1, path || s.id, ppath || ' \ ' || s.name
    FROM mm.travm s, task_tree tt WHERE s.parent_id = tt.id
)
SELECT id, name, parent_id, ppath FROM task_tree
ORDER BY 2 ASC;

Где используется в MIS3:

• небольшие древовидные справочники:
  • подразделения (mm.dept_dir)
  • травмы (mm.travm)
  • специализации (mm.spec)

Недостатки

• есть мнение, что на больших запросах потребляет много ресурсов

Использование ltree

Документация postgresql: http://www.postgresql.org/docs/current/static/ltree.html

Примеры:

-- Весь КЛДАР для региона
SELECT *
FROM mm.kladr AS m
WHERE m.path <@
      (
        SELECT path
        from mm.kladr k
        where k.name = 'Адыгея' and
              parent_id = 0
      )

-- выбор типов назначений из опреденной ветки
select id
from mm.naz_dict nd
where nd.path <@
      (
        select path
        from mm.naz_dict nnd
        where nnd.synonim = 'GROUP_INS'
      )

Где используется в MIS3

• КЛАДР (mm.kladr)
• Словарь назначений (mm.naz_dict)

Недостатки

• сложен в первоначальной програмировании (дополнительнее поле + тригера)

Использование tablefunc

см документацию postgresql для "connectby" http://www.postgresql.org/docs/9.4/static/tablefunc.html