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.