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

Установка

ltree является расширением PostgreSQL из пакета contrib. Для установки используется команда:

CREATE EXTENSION ltree;

Полная документация доступна на postgresql.org.

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

Создание таблицы для хранения иерархических данных:

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");

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

Получение информации о родителе с использованием функции subpath:

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

Альтернативный способ поиска родителя по пути:

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

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

Все потомки:

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} позволяет регулировать глубину уровней вложенности при выборке данных.