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

If you are facing the same problem, this query construction can help you:

DO $$
BEGIN
    WHILE (SELECT EXISTS(SELECT 1 from some_table where new_column IS NULL))
    LOOP
        UPDATE some_table AS st SET new_column = old_column
                     WHERE
                     st.id IN (  SELECT st2.id
                                 FROM some_table as st2
                                 WHERE st2.new_column IS NULL
                                 LIMIT 1000);
        COMMIT;
    END LOOP;
END $$;