CREATE TABLE "proba"."helper" (
  "child_id" INTEGER NOT NULL,
  "ancestor_id" INTEGER NOT NULL,
  "depth" INTEGER NOT NULL,
  CONSTRAINT "helper_idx" UNIQUE("ancestor_id", "child_id"),
  CONSTRAINT "helper_pkey" PRIMARY KEY("child_id", "ancestor_id"),
  CONSTRAINT "helper_chk" CHECK ((child_id <> ancestor_id) AND (depth > 0)),
  CONSTRAINT "ancestor_fk" FOREIGN KEY ("ancestor_id")
    REFERENCES "proba"."entity"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "child_fk" FOREIGN KEY ("child_id")
    REFERENCES "proba"."entity"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "ancestor_idx" ON "proba"."helper"
  USING btree ("ancestor_id");

CREATE INDEX "child_idx" ON "proba"."helper"
  USING btree ("child_id");

CREATE OR REPLACE FUNCTION "proba"."entity_ins" () RETURNS trigger AS
$body$
BEGIN
IF new.parent_id IS NOT NULL THEN
   INSERT INTO proba.helper(child_id,ancestor_id,depth)
     SELECT new.id,new.parent_id,1 UNION ALL
      SELECT new.id,ancestor_id,depth+1 FROM proba.helper WHERE child_id=new.parent_id;
  END IF;
  RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TABLE "proba"."entity" (
  "id" INTEGER NOT NULL,
  "title" TEXT NOT NULL,
  "parent_id" INTEGER,
  CONSTRAINT "entity_pkey" PRIMARY KEY("id"),
  CONSTRAINT "entity_chk" CHECK (parent_id <> id),
  CONSTRAINT "parent_fk" FOREIGN KEY ("parent_id")
    REFERENCES "proba"."entity"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "parent_idx" ON "proba"."entity"
  USING btree ("parent_id");

CREATE TRIGGER "entity_tr_del" AFTER UPDATE
ON "proba"."entity" FOR EACH ROW
EXECUTE PROCEDURE "proba"."entity_del"();

CREATE TRIGGER "entity_tr_ins" AFTER INSERT
ON "proba"."entity" FOR EACH ROW
EXECUTE PROCEDURE "proba"."entity_ins"();

CREATE OR REPLACE FUNCTION "proba"."entity_del" () RETURNS trigger AS
$body$
BEGIN
-- first remove edges between all old parents of node and its descendants
  DELETE FROM proba.helper WHERE (child_id=old.id OR child_id IN
   (SELECT child_id FROM proba.helper WHERE ancestor_id = old.id)
    ) AND ancestor_id IN
    (SELECT ancestor_id FROM proba.helper WHERE child_id = old.id);
  -- then add edges for all new parents ...
IF new.parent_id IS NOT NULL THEN
   INSERT INTO proba.helper(child_id,ancestor_id,depth)
     SELECT new.id,new.parent_id,1
      UNION ALL
      -- ... to node itself
      SELECT new.id,ancestor_id,depth+1 FROM proba.helper WHERE child_id=new.parent_id
      UNION ALL        
      -- ... and its descendants
      (                        
       SELECT child_id,new.parent_id,depth+1 FROM proba.helper WHERE ancestor_id=new.id
        UNION ALL
       SELECT child_id,ancestor_id,depth FROM
       (SELECT child_id FROM proba.helper WHERE ancestor_id=new.id) AS child
        CROSS JOIN
        (SELECT ancestor_id,depth+2 AS depth FROM proba.helper WHERE child_id=new.parent_id) AS parent
      );
  END IF;
  RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

if Node.NewParent <> Node.OldParent then // если изменился родитель
  // то меняем path
  if Node.NewParent = nil then // опечатка
    Node.Path := ''
  else
    Node.Path := Node.NewParent.Path + '\' + IntToStr(Node.NewParent.ID);

if Node.NewParent <> Node.OldParent then // если изменился родитель
  // то меняем path
  if Node.NewParent then
    Node.Path := ''
  else
    Node.Path := Node.NewParent.Path + '\' + IntToStr(Node.NewParent.ID);

--
  DECLARE SubItem CURSOR FOR SELECT ID, Item FROM #MenuLoad WHERE ID > 0 AND Modul = 0
  DECLARE @HID INT, @HTM VARCHAR(100)
  OPEN SubItem
  FETCH NEXT FROM SubItem INTO @HID, @HTM
  WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC UserMenuLoadNode @User, @HID, @HTM
    FETCH NEXT FROM SubItem INTO @HID, @HTM
  END
  CLOSE SubItem
  DEALLOCATE SubItem
--
  SELECT * FROM #MenuLoad ORDER BY 1
--
…
CREATE PROCEDURE UserMenuLoadNode @User INT, @HID INT, @HTM VARCHAR(100)
AS
  IF ( SELECT COUNT(*) FROM UsersMenu WHERE MenuUser = @User AND MenuHead = @HID ) > 0
    INSERT INTO #MenuLoad SELECT @HTM + '_' + STR(MenuOrder,3),
                               MenuCaption, ISNULL(MenuAction,0),
                               IdMenuItem, @HTM, MenuOrder, Note
                          FROM UsersMenu
                          WHERE MenuUser = @User AND MenuHead = @HID
--
GO