postgresql syntax while exists loop

Question

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.

Answer 1

Since WHILE accepts boolean-expression and feeds it to the SQL engine, the question is whether this is something that would be a valid SELECT statement. It seems it should be, but just in case you might wish to rephrase the condition to something like this:

WHILE (SELECT count(*) FROM OrgChart WHERE boss_emp_nbr=09999 AND emp_nbr > -99999) > 0 LOOP

Offhand, the endless loop might have more to do with the OrgChart UPDATE, but for that it'd help a bit to have the table structure and an explanation of what exactly are you trying to do.

Answer 2

Using WHILE EXISTS (...) is fine, since EXISTS (...) is a boolean SQL operator.

It's hard to see what you're actually trying to do (that wouldn't be better done as a recursive query), but I think your logic is wrong: for example, a table containing a single row with (emp_nbr=1,boss_emp_nbr=-99999) will cause an infinite loop as it continually tries to update all records where (boss_emp_nbr in (1)) to have emp_nbr=-99999 (with no effect).