From: nkunkov@optonline.net (Liza)
Subject: simple tree in postgress
Date: 16 Jan 2004 10:44:35 -0800
Organization: http://groups.google.com
Lines: 42
Message-ID: <3ff15ff1.0401161044.3acc25b2@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: "pgsql-general@postgresql.org.pgsql-sql"@postgresql.org

Hi, I'm a novice to postgress and need to constract a simple tree.
In my table I have:

regionID int
parentID int
name char

I need to do two functions:  find all the children of XXX parent and
find if an item is a child of an XXX parent.

I've looked in the discussions and I think there are at least two ways
of doing it:

(I'm pasting the following from one of the threads)

category_id | parent_id | sort_key |        name         
-------------+-----------+----------+---------------------
           0 |           | aa       | Top
          10 |         0 | aaab     | Propaganda
          43 |        10 | aaabaa   | Quotes
          12 |        10 | aaabab   | Presentations          
          70 |        10 | aaabai   | Waivers
           4 |        10 | aaabaj   | Legislation
          54 |         4 | aaabajaa | Youth in Politics
          36 |        10 | aaabak   | Statistics

This looks easy and I can see how to write queris using 'LIKE' on a
sort_key

The other way is to do preorder, inorder or postorder travrsals and
describe the level of the item and its traversal sequence number.  The
example I found was a bit confusing.  Can you point me to a place
where I can find more examples/explanations.

Are there other ways of doing it?  My tree would be small, maybe 5 - 7
levels and not too many leaves.  What are the advantages/disadvantages
of each method?
Is there a better way?  Does postgress offer any built in
functions/packages to work with simple trees?
I appreciate your advice.
Thank you
N.K.