I am working at function from Joe Celkos book - Trees and Hierarchies in SQL for Smarties
I am attempting to remove a subtree from an adjacency list but part my function isn't 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: may be the WHILE Is available correct to be used w/ postgresql? I seem to be tripping and becoming caught within an infinite loop within this part. Possibly there's a far more correct syntax I'm not aware of.
WHILE EXISTS (...) is okay, since
EXISTS (...) is really a boolean SQL operator.
It's difficult to determine what you are really attempting to do (that would not be better done like a recursive query), however i think your logic is wrong: for instance, a table that contains just one row with (emp_nbr=1,boss_emp_nbr=-99999) may cause an infinite loop because it constantly attempts to update all records where (boss_emp_nbr in (1)) to possess emp_nbr=-99999 (without any effect).
Since WHILE accepts boolean-expression and feeds it towards the SQL engine, now you ask , whether this really is something that might be a legitimate Choose statement. It appears it ought to be, but simply just in case you should rephrase the problem to something similar to this:
WHILE (SELECT count(*) FROM OrgChart WHERE boss_emp_nbr=09999 AND emp_nbr > -99999) > 0 LOOP
Offhand, the endless loop may have more related to the OrgChart UPDATE, but for your it'd help a little to achieve the table structure as well as an explanation of exactly what are you attempting to do.