Postgres Nested Tree

card_section_after_delete_2_tr.sql

CREATE OR REPLACE FUNCTION card_section_after_delete_func()
  RETURNS trigger AS
$BODY$
DECLARE
  _skew_tree INTEGER;
BEGIN
  PERFORM lock_card_section(OLD.tree);
  -- Проверяем, стоит ли выполнять триггер:
  IF OLD.trigger_for_delete = TRUE THEN RETURN OLD; END IF;
  -- Помечаем на удаление дочерние узлы:
  UPDATE card_section
  SET trigger_for_delete = TRUE,
      trigger_lock_update = TRUE
  WHERE
      tree = OLD.tree AND
      left_key > OLD.left_key AND
      right_key < OLD.right_key;
  -- Удаляем помеченные узлы:
  DELETE FROM card_section
  WHERE
      tree = OLD.tree AND
      left_key > OLD.left_key AND
      right_key < OLD.right_key;
  -- Убираем разрыв в ключах:
  _skew_tree := OLD.right_key - OLD.left_key + 1;
  UPDATE card_section
  SET left_key = CASE WHEN left_key > OLD.left_key
                        THEN left_key - _skew_tree
                      ELSE left_key
    END,
      right_key = right_key - _skew_tree,
      trigger_lock_update = TRUE
  WHERE right_key > OLD.right_key AND
      tree = OLD.tree;
  RETURN OLD;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION card_section_after_delete_func() OWNER TO CURRENT_USER;

CREATE TRIGGER card_section_after_delete_tr
  AFTER DELETE
  ON card_section
  FOR EACH ROW
EXECUTE PROCEDURE card_section_after_delete_func();


CREATE OR REPLACE FUNCTION card_section_after_delete_2_func()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
  PERFORM lock_card_section(OLD.tree);
  -- Убираем разрыв в ключах и сдвигаем дочерние узлы:
  UPDATE card_section
  SET left_key = CASE WHEN left_key < OLD.left_key
                        THEN left_key
                      ELSE CASE WHEN right_key < OLD.right_key
                                  THEN left_key - 1
                                ELSE left_key - 2
                        END
    END,
      "level" = CASE WHEN right_key < OLD.right_key
                       THEN "level" - 1
                     ELSE "level"
        END,
      parent_id = CASE WHEN right_key < OLD.right_key AND "level" = OLD.level + 1
                         THEN OLD.parent_id
                       ELSE parent_id
        END,
      right_key = CASE WHEN right_key < OLD.right_key
                         THEN right_key - 1
                       ELSE right_key - 2
        END,
      trigger_lock_update = TRUE
  WHERE (right_key > OLD.right_key OR
         (left_key > OLD.left_key AND right_key < OLD.right_key)) AND
      tree = OLD.tree;
  RETURN OLD;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION card_section_after_delete_2_func() OWNER TO CURRENT_USER;

CREATE TRIGGER card_section_after_delete_2_tr
  AFTER DELETE
  ON card_section
  FOR EACH ROW
EXECUTE PROCEDURE card_section_after_delete_2_func();

card_section_before_insert_tr.sql

CREATE OR REPLACE FUNCTION card_section_before_insert_func()
  RETURNS trigger AS
$BODY$
DECLARE
  _left_key       INTEGER;
  _level          INTEGER;
  _tmp_left_key   INTEGER;
  _tmp_right_key  INTEGER;
  _tmp_level      INTEGER;
  _tmp_id         INTEGER;
  _tmp_parent_id  INTEGER;
BEGIN
  PERFORM lock_card_section(NEW.tree);
  NEW.trigger_for_delete := FALSE;
  NEW.trigger_lock_update := FALSE;
  _left_key := 0;
  _level := 0;
  IF NEW.parent_id IS NOT NULL AND NEW.parent_id > 0 THEN
    SELECT right_key, "level" + 1
           INTO _left_key, _level
    FROM card_section
    WHERE id = NEW.parent_id AND
        tree = NEW.tree;
  END IF;
  IF NEW.left_key IS NOT NULL AND
     NEW.left_key > 0 AND
     (_left_key IS NULL OR _left_key = 0) THEN
    SELECT id, left_key, right_key, "level", parent_id
           INTO _tmp_id, _tmp_left_key, _tmp_right_key, _tmp_level, _tmp_parent_id
    FROM card_section
    WHERE tree = NEW.tree AND (left_key = NEW.left_key OR right_key = NEW.left_key);
    IF _tmp_left_key IS NOT NULL AND _tmp_left_key > 0 AND NEW.left_key = _tmp_left_key THEN
      NEW.parent_id := _tmp_parent_id;
      _left_key := NEW.left_key;
      _level := _tmp_level;
    ELSIF _tmp_left_key IS NOT NULL AND _tmp_left_key > 0 AND NEW.left_key = _tmp_right_key THEN
      NEW.parent_id := _tmp_id;
      _left_key := NEW.left_key;
      _level := _tmp_level + 1;
    END IF;
  END IF;
  IF _left_key IS NULL OR _left_key = 0 THEN
    SELECT MAX(right_key) + 1
           INTO _left_key
    FROM card_section
    WHERE tree = NEW.tree;
    IF _left_key IS NULL OR _left_key = 0 THEN
      _left_key := 1;
    END IF;
    _level := 0;
    NEW.parent_id := 0;
  END IF;
  NEW.left_key := _left_key;
  NEW.right_key := _left_key + 1;
  NEW."level" := _level;
  UPDATE card_section
  SET left_key = left_key +
                 CASE WHEN left_key >= _left_key
                        THEN 2
                      ELSE 0
                   END,
      right_key = right_key + 2,
      trigger_lock_update = TRUE
  WHERE tree = NEW.tree AND right_key >= _left_key;
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION card_section_before_insert_func() OWNER TO CURRENT_USER;

CREATE TRIGGER card_section_before_insert_tr
  BEFORE INSERT
  ON card_section
  FOR EACH ROW
EXECUTE PROCEDURE card_section_before_insert_func();

card_section_before_update_tr.sql

CREATE OR REPLACE FUNCTION card_section_before_update_func()
  RETURNS trigger AS
$BODY$
DECLARE
  _left_key       INTEGER;
  _level          INTEGER;
  _skew_tree      INTEGER;
  _skew_level     INTEGER;
  _skew_edit      INTEGER;
  _tmp_left_key   INTEGER;
  _tmp_right_key  INTEGER;
  _tmp_level      INTEGER;
  _tmp_id         INTEGER;
  _tmp_parent_id  INTEGER;
BEGIN
  PERFORM lock_card_section(OLD.tree);
  IF NEW.trigger_lock_update = TRUE THEN
    NEW.trigger_lock_update := FALSE;
    IF NEW.trigger_for_delete = TRUE THEN
      NEW = OLD;
      NEW.trigger_for_delete = TRUE;
      RETURN NEW;
    END IF;
    RETURN NEW;
  END IF;
  NEW.trigger_for_delete := FALSE;
  NEW.tree := OLD.tree;
  NEW.right_key := OLD.right_key;
  NEW."level" := OLD."level";
  IF NEW.parent_id IS NULL THEN NEW.parent_id := 0; END IF;
  IF NEW.parent_id = OLD.parent_id AND NEW.left_key = OLD.left_key
  THEN
    RETURN NEW;
  END IF;
  _left_key := 0;
  _level := 0;
  _skew_tree := OLD.right_key - OLD.left_key + 1;
  IF NEW.parent_id <> OLD.parent_id THEN
    IF NEW.parent_id > 0 THEN
      SELECT right_key, level + 1
             INTO _left_key, _level
      FROM card_section
      WHERE id = NEW.parent_id AND tree = NEW.tree;
    ELSE
      SELECT MAX(right_key) + 1
             INTO _left_key
      FROM card_section
      WHERE tree = NEW.tree;
      _level := 0;
    END IF;
    IF _left_key IS NOT NULL AND
       _left_key > 0 AND
       _left_key > OLD.left_key AND
       _left_key <= OLD.right_key
    THEN
      NEW.parent_id := OLD.parent_id;
      NEW.left_key := OLD.left_key;
      RETURN NEW;
    END IF;
  END IF;
  IF _left_key IS NULL OR _left_key = 0 THEN
    SELECT id, left_key, right_key, "level", parent_id
           INTO _tmp_id, _tmp_left_key, _tmp_right_key, _tmp_level, _tmp_parent_id
    FROM card_section
    WHERE tree = NEW.tree AND (right_key = NEW.left_key OR right_key = NEW.left_key - 1)
    LIMIT 1;
    IF _tmp_left_key IS NOT NULL AND _tmp_left_key > 0 AND NEW.left_key - 1 = _tmp_right_key THEN
      NEW.parent_id := _tmp_parent_id;
      _left_key := NEW.left_key;
      _level := _tmp_level;
    ELSIF _tmp_left_key IS NOT NULL AND _tmp_left_key > 0 AND NEW.left_key = _tmp_right_key THEN
      NEW.parent_id := _tmp_id;
      _left_key := NEW.left_key;
      _level := _tmp_level + 1;
    ELSIF NEW.left_key = 1 THEN
      NEW.parent_id := 0;
      _left_key := NEW.left_key;
      _level := 0;
    ELSE
      NEW.parent_id := OLD.parent_id;
      NEW.left_key := OLD.left_key;
      RETURN NEW;
    END IF;
  END IF;
  _skew_level := _level - OLD."level";
  IF _left_key > OLD.left_key THEN
    _skew_edit := _left_key - OLD.left_key - _skew_tree;
    UPDATE card_section
    SET left_key =  CASE WHEN right_key <= OLD.right_key
                           THEN left_key + _skew_edit
                         ELSE CASE WHEN left_key > OLD.right_key
                                     THEN left_key - _skew_tree
                                   ELSE left_key
                           END
      END,
        "level" =   CASE WHEN right_key <= OLD.right_key
                           THEN "level" + _skew_level
                         ELSE "level"
          END,
        right_key = CASE WHEN right_key <= OLD.right_key
                           THEN right_key + _skew_edit
                         ELSE CASE WHEN right_key < _left_key
                                     THEN right_key - _skew_tree
                                   ELSE right_key
                           END
          END,
        trigger_lock_update = TRUE
    WHERE tree = OLD.tree AND
        right_key > OLD.left_key AND
        left_key < _left_key AND
        id <> OLD.id;
    _left_key := _left_key - _skew_tree;
  ELSE
    _skew_edit := _left_key - OLD.left_key;
    UPDATE card_section
    SET
      right_key = CASE WHEN left_key >= OLD.left_key
                         THEN right_key + _skew_edit
                       ELSE CASE WHEN right_key < OLD.left_key
                                   THEN right_key + _skew_tree
                                 ELSE right_key
                         END
        END,
      "level" =   CASE WHEN left_key >= OLD.left_key
                         THEN "level" + _skew_level
                       ELSE "level"
        END,
      left_key =  CASE WHEN left_key >= OLD.left_key
                         THEN left_key + _skew_edit
                       ELSE CASE WHEN left_key >= _left_key
                                   THEN left_key + _skew_tree
                                 ELSE left_key
                         END
        END,
      trigger_lock_update = TRUE
    WHERE tree = OLD.tree AND
        right_key >= _left_key AND
        left_key < OLD.right_key AND
        id <> OLD.id;
  END IF;
  NEW.left_key := _left_key;
  NEW."level" := _level;
  NEW.right_key := _left_key + _skew_tree - 1;
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION card_section_before_update_func() OWNER TO CURRENT_USER;

CREATE TRIGGER card_section_before_update_tr
  BEFORE UPDATE
  ON card_section
  FOR EACH ROW
EXECUTE PROCEDURE card_section_before_update_func();

lock_card_section.sql

CREATE OR REPLACE FUNCTION lock_card_section(integer)
  RETURNS boolean AS
$BODY$
DECLARE tree_id ALIAS FOR $1;
        _id INTEGER;
BEGIN
  SELECT id
         INTO _id
  FROM card_section
  WHERE tree = tree_id FOR UPDATE;
  RETURN TRUE;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION lock_card_section(integer) OWNER TO CURRENT_USER;

table.sql

create table card_section
(
	id bigserial not null
		constraint card_section_pkey
			primary key,
	left_key integer not null,
	right_key integer not null,
	level integer default 0 not null,
	tree integer not null,
	parent_id bigint default 0 not null,
	trigger_lock_update boolean default false not null,
	trigger_for_delete boolean default false not null
);