I have a table with records that have a hierarchical relationship with one
another. Some records are children of other records which in turn can be
children of higher level records and so on until the highest level records
are reached. This is a simple tree relationship. It's being represented
by a second table with holds parentid-childid tuples so that to find the
children of a particular parent one only has to scan the second table's
first field for that given parent's id.
What I would like is a single recursive query that will recall all the
children of a given parent record. Right now I use JDBC and do my
recursion in the java code but this can result in numerous unnecassary
queries (and optimization is very difficult as it depends on the
breadth and the depth of the tree which are not well known parameters).
Is there a postgres command or a PL/SQL function someone could show me
that would offload this recursion to the database -- would this likeley
help performance (consider the case fo a single parent with a thousand
leaf-node children -- my current implementation scan's the parent-child
tree once for each child to determine if it is also a parent -- that's
1000 unnecassary SELECT statements)?
Any help/advice would be appreciated.
Thank you,
Sheer