"Charles F. Munat" <chas@xxxxxxxxx> writes: > Using pseudocode from Celko's "SQL for Smarties" book, I wrote the > following function that builds a path enumeration table. I hope to > trigger this function on the rare occasions that the organizations table > is updated. But when I run this function, it hangs. I think there might be something wrong with this query: > INSERT INTO organizations_path_enum > SELECT o1.parent_id, r1.child_id, (o1.depth + 1) > FROM organizations_path_enum o1, relationships r1 > -- advance existing paths by one level > WHERE EXISTS (SELECT * FROM organizations_path_enum AS o2 > WHERE r1.parent_id = o2.child_id) > -- insert only new rows into the table > AND NOT EXISTS (SELECT * FROM organizations_path_enum AS o3 > WHERE o1.parent_id = o3.parent_id AND r1.child_id = o3.child_id); I'm not totally clear on what this is supposed to accomplish, but it seems like there should be some join clause between o1 and r1. regards, tom lane
