Thread: Determining parent.
Greetings to all.
This is more of a SQL question than a direct PostgreSQL question.
Say I have 1 table... Objects has columns IDX int, Parent int, Deleted bit, I have the objects table loaded with data, some records will have a Parent record that references the IDX of the object table. Given one object, how can I determine all the whole tree of all sub-objects and not just the first child?
IE, below. Say I want to know all objects under IDX 1, no problem, but how do I then drill down the query to get all children? I did something like this with Oracle once using a CONNECT BY clause but I'm not too sure how to do that here.
idx | parent | deleted
-----+--------+---------
3 | 2 | 0
101 | 1 | 0
103 | 1 | 0
104 | 1 | 0
105 | 1 | 0
107 | 1 | 0
108 | 1 | 0
111 | 1 | 0
109 | 1 | 0
113 | 1 | 0
115 | 1 | 0
1 | 0 | 0
2 | 1 | 0
117 | 1 | 0
4 | 1 | 0
118 | 117 | 0
-----+--------+---------
3 | 2 | 0
101 | 1 | 0
103 | 1 | 0
104 | 1 | 0
105 | 1 | 0
107 | 1 | 0
108 | 1 | 0
111 | 1 | 0
109 | 1 | 0
113 | 1 | 0
115 | 1 | 0
1 | 0 | 0
2 | 1 | 0
117 | 1 | 0
4 | 1 | 0
118 | 117 | 0
Does this make sense to anyone?
Paul
You need to build a recursive function.
function get_all_kids ($count as integer, $startnode as integer) {
$sql = "select idx, parnet from objects where deleted = false and parent = startnode"
{execute $sql}
{return if no rows}
{loop through results}
print "child at level $count in tree = $idx";
get_all_kids($count +1, $idx) # note this is where we recurse (recurse = function calls itself)
{end loop}
{return}
}
Recursion is cool, but it can chew up all the memory on the system in a hurry. You should read up on recursion.
--rob
----- Original Message -----From: Paul SkinnerSent: Friday, January 05, 2001 12:43 PMSubject: Determining parent.Greetings to all.This is more of a SQL question than a direct PostgreSQL question.Say I have 1 table... Objects has columns IDX int, Parent int, Deleted bit, I have the objects table loaded with data, some records will have a Parent record that references the IDX of the object table. Given one object, how can I determine all the whole tree of all sub-objects and not just the first child?IE, below. Say I want to know all objects under IDX 1, no problem, but how do I then drill down the query to get all children? I did something like this with Oracle once using a CONNECT BY clause but I'm not too sure how to do that here.idx | parent | deleted
-----+--------+---------
3 | 2 | 0
101 | 1 | 0
103 | 1 | 0
104 | 1 | 0
105 | 1 | 0
107 | 1 | 0
108 | 1 | 0
111 | 1 | 0
109 | 1 | 0
113 | 1 | 0
115 | 1 | 0
1 | 0 | 0
2 | 1 | 0
117 | 1 | 0
4 | 1 | 0
118 | 117 | 0Does this make sense to anyone?Paul