I'm working at function from Joe Celkos book - Trees and Hierarchies in SQL for Smarties
I'm trying to delete a subtree from an adjacency list but part my function is not working yet.
WHILE EXISTS –– mark leaf nodes
(SELECT *
FROM OrgChart
WHERE boss_emp_nbr = −99999
AND emp_nbr > −99999)
LOOP –– get list of next level subordinates
DELETE FROM WorkingTable;
INSERT INTO WorkingTable
SELECT emp_nbr FROM OrgChart WHERE boss_emp_nbr = −99999;
–– mark next level of subordinates
UPDATE OrgChart
SET emp_nbr = −99999
WHERE boss_emp_nbr IN (SELECT emp_nbr FROM WorkingTable);
END LOOP;
my question: is the WHILE EXISTS correct for use w/ postgresql? I appear to be stumbling and getting caught in an infinite loop in this part. Perhaps there is a more correct syntax I am unaware of.
