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.

Using 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.