Skip site navigation (1) Skip section navigation (2)

Re: Determining parent.

From: "rob" <rob(at)cabrion(dot)com>
To: "Paul Skinner" <skin(at)skin(dot)dhs(dot)org>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Determining parent.
Date: 2001-01-06 22:40:05
Message-ID: 002801c07831$9e008a20$4100fd0a@cabrion.org (view raw or flat)
Thread:
Lists: 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 ----- 
  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
   

      

In response to

pgsql-novice by date

Next:From: Mike FriesenDate: 2001-01-06 22:54:40
Subject: Join question
Previous:From: Brett W. McCoyDate: 2001-01-06 21:51:51
Subject: Re: Create Table Scripts

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group