Re: Determining parent. - Mailing list pgsql-novice

From rob
Subject Re: Determining parent.
Date
Msg-id 002801c07831$9e008a20$4100fd0a@cabrion.org
Whole thread Raw
In response to Determining parent.  ("Paul Skinner" <skin@skin.dhs.org>)
List pgsql-novice
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 -----
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
 
 
   

pgsql-novice by date:

Previous
From: "Brett W. McCoy"
Date:
Subject: Re: Create Table Scripts
Next
From: Chuck Kimber
Date:
Subject: Re: Create Table Scripts