pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtree-structured query
2+ messages / 2 participants
[nested] [flat]
* tree-structured query
@ 2006-09-29 22:00 chester c young <chestercyoung@yahoo.com>
2006-09-30 22:29 ` Re: tree-structured query Ragnar <gnari@hive.is>
0 siblings, 1 reply; 2+ messages in thread
From: chester c young @ 2006-09-29 22:00 UTC (permalink / raw)
To: pgsql-sql
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,
select name from t where exists <ancestor or descendent>
thanks for insight
---------------------------------
Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: tree-structured query
2006-09-29 22:00 tree-structured query chester c young <chestercyoung@yahoo.com>
@ 2006-09-30 22:29 ` Ragnar <gnari@hive.is>
0 siblings, 0 replies; 2+ messages in thread
From: Ragnar @ 2006-09-30 22:29 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-09-30 22:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-29 22:00 tree-structured query chester c young <chestercyoung@yahoo.com>
2006-09-30 22:29 ` Ragnar <gnari@hive.is>
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