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

Search Discussions

  • Rob at Jan 6, 2001 at 10:35 pm
    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@postgresql.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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 5, '01 at 5:35p
activeJan 6, '01 at 10:35p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Paul Skinner: 1 post Rob: 1 post

People

Translate

site design / logo © 2022 Grokbase