postgresql syntax while exists loop
Posted
by veilig
on Stack Overflow
See other posts from Stack Overflow
or by veilig
Published on 2010-05-04T17:18:05Z
Indexed on
2010/05/04
18:18 UTC
Read the original article
Hit count: 297
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.
© Stack Overflow or respective owner