Postgresql while exists syntax
I am working on a function from Joe Selkos's book - Trees and Hierarchies in SQL for Smarties
I am trying to remove a subtree from the adjacency list, but part of 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, is WHILE EXISTS used correctly for using w / postgresql? I seem to stumble and end up in an endless loop in this part. Perhaps there is a more correct syntax that I am not aware of.
a source to share
Usage is WHILE EXISTS (...)
fine as it EXISTS (...)
is a boolean SQL statement.
It's hard to figure out what you are actually trying to do (it's not better to do it as a recursive query), but I think your logic is wrong: for example, a table containing one row with (emp_nbr = 1, boss_emp_nbr = -99999) will cause an infinite loop as it constantly tries to update all records where (boss_emp_nbr in (1)) has emp_nbr = -99999 (no effect).
a source to share
Since WHILE takes a boolean expression and passes it to the SQL engine, the question is whether this is something that would be a valid SELECT statement. It seems like it should be, but just in case, you can rephrase the condition something like this:
WHILE (SELECT count(*) FROM OrgChart WHERE boss_emp_nbr=09999 AND emp_nbr > -99999) > 0 LOOP
An offline, infinite loop might have more to do with OrgChart UPDATE, but for that it helps a little to have a table structure and an explanation of what exactly you are trying to do.
a source to share