467,083 Members | 1,259 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,083 developers. It's quick & easy.

simple tree in postgress

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.
Jul 19 '05 #1
  • viewed: 2264
Share:
1 Reply
In article <3f**************************@posting.google.com >,
nk*****@optonline.net (Liza) wrote:
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.


Liza, there is an excellent series of articles by Joe Celko on how to
use SQL for tree structures. I got his method working about a yr. ago
and dig it up for an example if you need it.

The articles start at:
http://www.intelligententerprise.com...celko1_1.shtml

Boyd

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by sathyashrayan | last post: by
67 posts views Thread by Bob Powell | last post: by
1 post views Thread by Liza | last post: by
1 post views Thread by Praveen | last post: by
1 post views Thread by hn.ft.pris@gmail.com | last post: by
4 posts views Thread by Travis | last post: by
1 post views Thread by HighBuddha | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.