Управление ltree с использованием алгоритма Adjacency List

Задача

Требуется «прозрачное» управление деревом с комбинацией Materialized Path и Adjacency List. Преимущества подхода:

• Прямая связь с родителем и возможность использовать FOREIGN KEY.
• Упрощённое указание ID родителя без формирования пути.

Универсальная хранимая процедура

Таблица должна содержать следующие поля:

CREATE TABLE "public"."section" (
    "id"            SERIAL,
    "parent"        INTEGER,
    "root"          INTEGER             NOT NULL,
    "path"          "public"."ltree"    NOT NULL,
        CONSTRAINT "section__path__key" UNIQUE("path"),
        CONSTRAINT "section__pkey" PRIMARY KEY("id"),
        CONSTRAINT "section__section__fkey" FOREIGN KEY ("parent")
        REFERENCES "public"."section"("id")
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

Где:

• id — идентификатор записи.
• parent — ID родителя (NULL для корневых узлов).
• root — идентификатор дерева.
• path — материализованный путь.

Триггерная функция

CREATE OR REPLACE FUNCTION "public"."ltree_modify" ()
RETURNS TRIGGER AS
$body$
DECLARE
    prnt            RECORD;
    ppath           ltree           := '';
BEGIN
    IF tg_op <> 'DELETE' THEN
        IF NEW.parent IS NOT NULL THEN
            EXECUTE 'SELECT * FROM ' || tg_table_schema || '.' || tg_table_name ||
                       ' WHERE id = ' || NEW.parent INTO prnt;
            IF prnt.id IS NULL THEN
                RAISE EXCEPTION 'parent is not exists: %', NEW.parent;
            END IF;
            IF NEW.root IS NULL THEN
                NEW.root := prnt.root;
            ELSE
                IF NEW.root <> prnt.root THEN
                    RAISE EXCEPTION 'different roots: % <=> %',
                        prnt.root, NEW.root;
                END IF;
            END IF;
            ppath := prnt.path;
        ELSE
            IF NEW.root IS NULL THEN
                RAISE EXCEPTION 'root is required';
            END IF;
        END IF;
        NEW.path := ppath || NEW.id::text;
    END IF;

    IF tg_op = 'UPDATE' THEN
        IF NEW.path <> OLD.path AND NEW.path <@ OLD.path THEN
            RAISE EXCEPTION 'can''t move node: % => %', OLD.path, NEW.path;
        END IF;
    END IF;

    IF tg_op <> 'DELETE' THEN RETURN NEW; ELSE RETURN OLD; END IF;
END;
$body$
LANGUAGE 'plpgsql';

Создание триггера

CREATE TRIGGER "section_tr" BEFORE INSERT OR UPDATE OR DELETE
ON "public"."section" FOR EACH ROW
EXECUTE PROCEDURE "public"."ltree_modify"();

Обновление потомков при изменении родителя

CREATE OR REPLACE FUNCTION "public"."ltree_after_update" ()
RETURNS TRIGGER AS
$body$
BEGIN
    IF NEW.path <> OLD.path THEN
        EXECUTE 'UPDATE ' || tg_table_schema || '.' || tg_table_name ||
                   ' SET path = NULL
                     WHERE parent = ' || NEW.id;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER "section_tr1" AFTER UPDATE
ON "public"."section" FOR EACH ROW
EXECUTE PROCEDURE "public"."ltree_after_update"();