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 $$;

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

Answer 3

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
     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;

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.