A bunch of stuff is checked in and working, but thanks to David's inspired hack that taught me how to declare a recursive SQL function I've broken things again :)
Actually my checked-out copy's pretty good, and I'll be committing my changes tomorrow.
Using Dave's hack makes finding parents very cool, though the actual technique's quite different than Dave's example, being based entirely on the tree_sortkey for the child node:
select parent_nodes.*
from (select tree_ancestor_keys(acs_object_get_tree_sortkey(:child_object_id)) as tree_sortkey) parent_keys,
acs_objects parent_nodes
where parent_nodes.object_id = parent_keys.object_id;
This query is blindingly fast, using the index on tree_sortkey to find the right nodes from the parent_nodes relation.
And it's even somewhat readable, a bonus! tree_ancestor_keys is the recursive SQL function, and acs_object_get_root_key is necessary because, well, you can play around and figure out why yourselves :)
Kudos to Dave once again for what is truly a deviously sneakily deliciously decadent hack on the PG internal tables!
