Использование расширения ltree в PostgreSQL

Установка

ltree является дополнением PostgreSQL и входит в пакет contrib, поэтому изначально оно не включено в стандартный пакет для *nix систем. Для установки выполняем команду в клиента psql в нужной базе данных:

CREATE EXTENSION ltree;

Исходная документация находится по адресу:

https://www.postgresql.org/docs/current/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 работать с материализованными путями одно удовольствие.