DOC.PROTOTYPES.RU

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

Установка

ltree является дополнением PostgreSQL и входит в пакет contrib, поэтому изначально оно не включено в стандартный пакет для *nix систем. Для установки запускаем в нужной базе данных .sql-файл /usr/local/share/postgresql/contrib/ltree.sql (C:\Program Files\PostgreSQL\8.4\share\contrib\ltree.sql для Windows).

Исходная документация находится по адресу: http://www.postgresql.org/docs/8.4/static/ltree.html. Я же рассмотрю отдельные моменты, которые не описаны в "родной" документации.

Исходные данные

Создаем простую таблицу без излишеств:

CREATE TABLE "public"."mp_tree" (
  "id"          INTEGER             DEFAULT nextval('mpath_id_seq'::regclass) NOT NULL, 
  "name"        TEXT, 
  "mpath"       "public"."ltree"    NOT NULL, 
  CONSTRAINT "mpath_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;

CREATE UNIQUE INDEX "mp_tree_idx" ON "public"."mp_tree"
  USING btree ("mpath" "public"."ltree_ops");

CREATE INDEX "mp_tree_idx1" ON "public"."mp_tree"
  USING gist ("mpath" "public"."gist_ltree_ops");

В качестве элементов материализованного пути будет id.

Родительские узлы

Добавление в результат выборки id и mpath родителя:

SELECT 
    m.*,
    subpath(mpath, -2, -1) AS pid,
    subpath(mpath, 0, -1) AS pmpath
FROM mp_tree AS m;

Если в качестве элементов материализованного пути используются неуникальные значения, то родителя лучше искать по mpath:

SELECT m.*
FROM mp_tree AS m
WHERE m.mpath = subpath([mpath текущего узла], 0, -1);

Хотя можно и так:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath <@ [mpath текущего узла] AND m.mpath <> [mpath текущего узла]
    ORDER BY m.mpath DESC
    LIMIT 1;

правда при этом стоит не забывать, что "<@" верно и для текущего узла. Соответсвенно, убрав LIMIT мы получим всю родительскую ветку от текущего узла.

Подчиненные узлы

Все потомки:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath <@ [mpath текущего узла]
    ORDER BY m.mpath;

Но в результат попадет и текущий узел, поэтому либо его исключать дополнительным условием, либо потомков выбирать так:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath ~ '[mpath текущего узла].*'
    ORDER BY m.mpath;

Прямые потомки:

SELECT m.*
    FROM mp_tree AS m
    WHERE m.mpath ~ '[mpath текущего узла].*{1}'
    ORDER BY m.mpath;

Собственно, выражением .*{n} можно регулировать глубину уровней вложенности для выборки.

Как видно, с помощью ltree работать с материализованными путями одно удовольствие.

• Управление "чистым" алгоритмом Materialized Path
• Управление с использованием алгоритма Adjacency List