postgresql syntax while exists loop

Posted by veilig on Stack Overflow See other posts from Stack Overflow or by veilig
Published on 2010-05-04T17:18:05Z Indexed on 2010/05/04 18:18 UTC
Read the original article Hit count: 297

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.

© Stack Overflow or respective owner

Related posts about postgresql

Related posts about sql