Управление «чистым» алгоритмом Materialized Path

Задача

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

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

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

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

Требуется создавать 2 индекса на поле path для работы с типом ltree и text одновременно. В качестве элементов материализованного пути используются id узлов. Триггеры обеспечивают целостность данных.

Триггер на вставку

CREATE OR REPLACE FUNCTION "public"."_mpath_insert_before_trigger" ()
RETURNS TRIGGER AS
$body$
DECLARE
    new_mpath       ltree;
BEGIN
    IF NEW.mpath IS NOT NULL THEN
        new_mpath := CASE WHEN NEW.id::text = subpath(NEW.mpath, -1, 1)::text
                          THEN subpath(NEW.mpath, 0, -1)
                          ELSE NEW.mpath
                     END;
        SELECT mp.mpath INTO new_mpath
            FROM mp_tree AS mp
            WHERE mp.mpath = new_mpath OR mp.id::text = new_mpath::text;
        IF new_mpath IS NULL OR new_mpath = '' THEN
            NEW.mpath := NEW.id::text;
        ELSE
            NEW.mpath := new_mpath || NEW.id::text;
        END IF;
    ELSE
        NEW.mpath := NEW.id::text;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER "mpath_insert_before" BEFORE INSERT
ON "public"."mp_tree" FOR EACH ROW
EXECUTE PROCEDURE "public"."_mpath_insert_before_trigger"();

Триггер на обновление

CREATE OR REPLACE FUNCTION "public"."_mpath_update_after_trigger" ()
RETURNS TRIGGER AS
$body$
DECLARE
    tid         INTEGER;
BEGIN
    NEW.id := OLD.id;
    NEW.mpath := CASE WHEN NEW.mpath IS NULL
                      THEN NEW.id::text
                      ELSE NEW.mpath::text
                 END;
    IF NEW.mpath <> OLD.mpath THEN
        IF
            NEW.mpath ~ ('*.' || NEW.id::text || '.*{1,}')::lquery OR
            NEW.mpath ~ ('*.!' || NEW.id::text)::lquery
        THEN
            RAISE EXCEPTION 'Bad mpath!';
        END IF;
        IF nlevel(NEW.mpath) > 1 THEN
            SELECT m.id INTO tid
                FROM mp_tree AS m
                WHERE m.mpath = subpath(NEW.mpath, 0, nlevel(NEW.mpath) - 1);
            IF tid IS NULL THEN
                RAISE EXCEPTION 'Bad parent!';
            END IF;
        END IF;
        UPDATE mp_tree
            SET mpath = NEW.mpath || id::text
            WHERE mpath ~ (OLD.mpath::text || '.*{1}')::lquery;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER "mpath_update_after" AFTER UPDATE
ON "public"."mp_tree" FOR EACH ROW
EXECUTE PROCEDURE "public"."_mpath_update_after_trigger"();

Триггер на удаление

CREATE OR REPLACE FUNCTION "public"."_mp_tree_delete_after_trigger" ()
RETURNS TRIGGER AS
$body$
BEGIN
    DELETE FROM mp_tree
        WHERE mpath ~ (OLD.mpath::text || '.*{1}')::lquery;
    RETURN OLD;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER "mp_tree_delete_after" AFTER DELETE
ON "public"."mp_tree" FOR EACH ROW
EXECUTE PROCEDURE "public"."_mp_tree_delete_after_trigger"();

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