From: Ragnar <gnari@hive.is>
To: chester c young <chestercyoung@yahoo.com>
Cc: sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: tree-structured query
Date: Sat, 30 Sep 2006 22:29:53 +0000
Message-ID: <1159655393.9076.13.camel@localhost.localdomain> (raw)
In-Reply-To: <20060929220015.40971.qmail@web54309.mail.yahoo.com>
References: <20060929220015.40971.qmail@web54309.mail.yahoo.com>
On fös, 2006-09-29 at 15:00 -0700, chester c young wrote:
> in a simple tree structured table
>
> table t(
> id primary key,
> pnt_id references t( id ),
> name
> );
>
> does anyone know an easy howbeit sneaky way of determining ancestory
> and decendency without recursive functions,
how about
CREATE TABLE ancestry (
ans_id int, desc_id int
)
for each record of t ,
for each ancestor of id,
insert a record (ans_id,id) into anscestry
this can be maintained by application, or by triggers.
to get all ancestors of a particular id X:
SELECT name from t JOIN ancestry ON (id=ans_id)
WHERE desc_id=X;
to get descendents:
SELECT name from t JOIN ancestry ON (id=desc_id)
WHERE ans_id=X;
indexes on ancestry(ans_id) and ancestry(desc_id)
might be called for.
hope this helps
gnari
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gnari@hive.is, chestercyoung@yahoo.com
Subject: Re: tree-structured query
In-Reply-To: <1159655393.9076.13.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox