CREATE TABLE FIAS$ADDRESSES (
    OBJECT_ID    D$GUID NOT NULL /* D$GUID = CHAR(16) */,
    PARENT_FK    D$GUID /* D$GUID = CHAR(16) */,
    "LEVEL"      D$SMALLINT NOT NULL /* D$SMALLINT = SMALLINT */,
    NS_ID        D$INTEGER DEFAULT 0x7FFFFFFF NOT NULL /* D$INTEGER = INTEGER */,
    NS_LEFT      D$INTEGER DEFAULT 0 NOT NULL /* D$INTEGER = INTEGER */,
    NS_RIGHT     D$INTEGER DEFAULT 0 NOT NULL /* D$INTEGER = INTEGER */
);
ALTER TABLE FIAS$ADDRESSES ADD CONSTRAINT FIAS$ADDRESSES$PK PRIMARY KEY (OBJECT_ID);
ALTER TABLE FIAS$ADDRESSES ADD CONSTRAINT FIAS$ADDRESSES_FIAS$ADDRESSES FOREIGN KEY (PARENT_FK) REFERENCES FIAS$ADDRESSES (OBJECT_ID);
CREATE INDEX FIAS$ADDRESSES$IDX_PFK_LVL    ON FIAS$ADDRESSES (PARENT_FK, "LEVEL");
CREATE INDEX FIAS$ADDRESSES$TMP_NS_ID      ON FIAS$ADDRESSES (NS_ID);

CREATE OR ALTER PROCEDURE FIAS$BUILD$08
AS
DECLARE VARIABLE OBJECT_ID D$GUID;
DECLARE VARIABLE COUNTER D$INTEGER;
DECLARE VARIABLE MAX_COUNTER D$INTEGER;
DECLARE VARIABLE CURRENT_ID D$INTEGER;
BEGIN
  INSERT INTO FIAS$ADDRESSES (OBJECT_ID, "LEVEL", REGION_CODE, NAME, FORMAL_NAME, PREFIX_NAME, KLADR_ID, NS_ID, NS_LEFT, NS_RIGHT)
    VALUES (CHAR_TO_UUID('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'), 1, 0, 'ROOT', 'ROOT', 'АО', '', 0, 1, 0);
  UPDATE FIAS$ADDRESSES SET
    PARENT_FK = CHAR_TO_UUID('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF')
  WHERE PARENT_FK IS NULL
    AND OBJECT_ID <> CHAR_TO_UUID('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF');

  CURRENT_ID  = 0;
  COUNTER     = 0;
  MAX_COUNTER = (SELECT 2 * COUNT(1) FROM FIAS$ADDRESSES);

  WHILE (COUNTER < (MAX_COUNTER - 1)) DO
  BEGIN
    COUNTER   = COUNTER + 1;
    OBJECT_ID = NULL;
    SELECT FIRST 1 P.OBJECT_ID
    FROM FIAS$ADDRESSES A
    INNER JOIN FIAS$ADDRESSES P ON P.PARENT_FK = A.OBJECT_ID
      AND P.NS_LEFT = 0
    WHERE A.NS_ID = :CURRENT_ID
    INTO :OBJECT_ID;

    IF (OBJECT_ID IS NOT NULL) THEN
    BEGIN -- PUSH WHEN TOP HAS SUBORDINATES, SET LEFT_KEY VALUE
      UPDATE FIAS$ADDRESSES SET
        NS_ID   = :CURRENT_ID + 1,
        NS_LEFT = :COUNTER
      WHERE OBJECT_ID = :OBJECT_ID;
      CURRENT_ID = CURRENT_ID + 1;
    END
    ELSE
    BEGIN  -- POP THE STACK AND SET RIGHT_KEY VALUE
      UPDATE FIAS$ADDRESSES SET
        NS_ID    = -NS_ID, -- POPS THE STACK
        NS_RIGHT = :COUNTER
      WHERE NS_ID = :CURRENT_ID;
      CURRENT_ID = CURRENT_ID - 1;
    END
  END

  UPDATE FIAS$ADDRESSES SET
    PARENT_FK = NULL
  WHERE PARENT_FK = CHAR_TO_UUID('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF');
  DELETE FROM FIAS$ADDRESSES
  WHERE OBJECT_ID = CHAR_TO_UUID('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF');
END^

    SELECT FIRST 1 P.OBJECT_ID
    FROM FIAS$ADDRESSES A
    INNER JOIN FIAS$ADDRESSES P ON P.PARENT_FK = A.OBJECT_ID
      AND P.NS_LEFT = 0
    WHERE A.NS_ID = :CURRENT_ID
    INTO :OBJECT_ID;

PLAN JOIN (A INDEX (FIAS$ADDRESSES$TMP_NS_ID), P INDEX (FIAS$ADDRESSES_FIAS$ADDRESSES))

      WHILE (:PREV_PARENT IS not null) DO
      BEGIN
        UPDATE T SET R = :N 
         WHERE ID = :PREV_ID
        RETURNING PARENT_ID INTO :PREV_PARENT;

        N = N + 1;
        PREV_ID = PREV_PARENT;
      END
  --UPDATE T SET R = :N
   --WHERE ID = :ID;

  --UPDATE T SET R = :N + 1
   --WHERE PARENT_ID IS NULL;