Re: Determining parent.

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 Skinner
To: pgsql-novice(at)postgresql(dot)org
Sent: Friday, January 05, 2001 12:43 PM
Subject: 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

Does this make sense to anyone?

Paul