From: | Ragnar <gnari(at)hive(dot)is> |
---|---|
To: | chester c young <chestercyoung(at)yahoo(dot)com> |
Cc: | sql pgsql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: tree-structured query |
Date: | 2006-09-30 22:29:53 |
Message-ID: | 1159655393.9076.13.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-10-02 03:56:09 | Re: regexp_replace usage |
Previous Message | Alexander Ilyin | 2006-09-30 08:43:40 | How to FindNearest |