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

Задача

«Прозрачное» управление, совместно с алгоритмом Adjacency List.

На самом деле, мне лично смешивание Materialized Path и Adjacency List при использовании ltree представляется сомнительным решением, особенно учитывая то, что в большинстве случаем материализованный путь составляется из ID узлов, и ID родителя получить не представляется сложной задачей. Тем не менее, определенные плюсы от этого есть, а именно:

• Прямая связь с родителем и возможность навесить FOREIGN KEY;
• Возможность просто указывать id родителя не тратя время на формирование материализованного пути изменяемого узла;

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

Вообще идея подобной процедуры не моя, а моего коллеги, но он так до конца её и не реализовал. Эту реализацию я сделал в том числе и для него.

Таблица должна иметь следующие свойства:

• материализованных путь формируется из 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
            NOT DEFERRABLE
) WITHOUT OIDS;

Где:

• id — собственно id;
• pid — id родителя, NULL если узел корневой;
• root — идентификатор дерева, обязательное поле. Если в таблице будет храниться одно дерево, то для него можно поставить значение по умолчанию, например, 1;
• path — материализованный путь;

Соответственно, внешний ключ на каскадное обновление и удаление для поля pid.

Сама хранимая процедура выглядит так:

CREATE OR REPLACE FUNCTION "public"."ltree_modify" (
)
RETURNS TRIGGER AS
$body$
DECLARE
    prnt            RECORD;
    ppath           ltree           := '';
BEGIN 
-- Операции использующие переменную NEW
    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;
-- Операции использующие переменную NEW и OLD
    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';

Ввиду того, что данная процедура будет использоваться при любом изменении таблицы, операции в ней разделены по использованию переменных NEW и OLD, так как при обращении к переменной NEW во время удаления даст ошибку, так как и обращение к переменной OLD во время вставки.

В процедуре предусмотрена работа с переменными таблицами и схемами, в зависимости от триггера её (процедуру) вызвавшего, что дает возможность использовать данную процедуру в нескольких таблицах одновременно. Но данное решение не совсем оптимально в так как запросы внутри процедуры не компилируются сразу а в процессе выполнения.

Создаем триггер для таблицы.

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

Увы, при изменении подчиненности узла, вышеуказанный триггер не распространится на детей, соответственно потребуется произвести ручное обновление (внешний ключ не поможет так как id не обновляется):

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

То что удаление подчиненных записей производится до удаления родительской тоже не совсем хорошо, но опять же, все зависит от того, насколько часто производится удаление.