Adjacency List Model to Nested Set Model in pgAdmin - Mailing list pgadmin-support

Hello,

I am trying to convert an adjacency list model to a nested set model in
pgAdmin. I have found a website
(http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html) that show's a method
for doing this, and suggests the following:

6. To convert an adjacency list model into a nested set model, use a push
down stack algorithm. Assume that we have these tables:

-- Tree holds the adjacency model

CREATE TABLE Tree
(emp CHAR(10) NOT NULL,boss CHAR(10));

INSERT INTO Tree
SELECT emp, boss FROM Personnel;

-- Stack starts empty, will holds the nested set model
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,emp CHAR(10) NOT NULL,lft INTEGER,rgt INTEGER);

BEGIN ATOMIC
DECLARE counter INTEGER;
DECLARE max_counter INTEGER;
DECLARE current_top INTEGER;

SET counter = 2;
SET max_counter = 2 * (SELECT COUNT(*) FROM Tree);
SET current_top = 1;

INSERT INTO Stack
SELECT 1, emp, 1, NULL FROM TreeWHERE boss IS NULL;

DELETE FROM TreeWHERE boss IS NULL;

WHILE counter <= (max_counter - 2)
LOOP IF EXISTS (SELECT *                  FROM Stack AS S1, Tree AS T1                 WHERE S1.emp = T1.boss
       AND S1.stack_top = current_top)    THEN    BEGIN -- push when top has subordinates, set lft value      INSERT
INTOStack      SELECT (current_top + 1), MIN(T1.emp), counter, NULL        FROM Stack AS S1, Tree AS T1       WHERE
S1.emp= T1.boss         AND S1.stack_top = current_top;
 
       DELETE FROM Tree        WHERE emp = (SELECT emp                       FROM Stack                      WHERE
stack_top= current_top + 1);
 
       SET counter = counter + 1;       SET current_top = current_top + 1;    END    ELSE    BEGIN  -- pop the stack
andset rgt value      UPDATE Stack         SET rgt = counter,             stack_top = -stack_top -- pops the stack
WHERE stack_top = current_top      SET counter = counter + 1;      SET current_top = current_top - 1;    END IF;END
LOOP;
END;

I am new to PostgreSQL, and am not sure how to convert it into the proper
language. I keep running into syntax errors. If anybody feels like tackling
this issue, please let me know!

-- 
View this message in context:
http://old.nabble.com/Adjacency-List-Model-to-Nested-Set-Model-in-pgAdmin-tp28467334p28467334.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.