From: | h012(at)ied(dot)com |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | retrieving all rows from a "tree" in one select - how ? |
Date: | 2002-08-09 21:36:35 |
Message-ID: | no.Yo.N.nN.0208091650190.2280-100000@business.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I realize that a relational database may not be ideal for storing (and
retrieving) tree-like strucutres, but it looks like you guys are doing
with PostgreSQL the impossible anyway.
Having table t of all nodes:
CREATE SEQUENCE nodeIDseq START 1;
CREATE TABLE t(
id int PRIMARY KEY DEFAULT NEXTVAL('nodeIDseq'),
parent int REFERENCES t,
mydata int4
);
INSERT INTO t VALUES (0,0);
I was wondering whether there is a known (and perhaps working) way to do
things like:
-- select a tree starting with node 1234 and all its descendants:
SELECT * FROM t WHERE id=1234 OR ANCESTOR(t.parent) IS 1234;
and
-- select the path from tree node 2345 to the root
SELECT * FROM t WHERE id=2345 OR DESCENTANT(t.parent) IS 2345;
(I've seen some terse soutions at
http://www.brasileiro.net/postgres/cookbook/view-recipes.adp?section_id=2&format=long
but they don't seem to be complete.)
(Also I've looket at ltrees from GiST, but "ltree" seems to require that
the ID attribute contains all ancestors.)
Thanks,
John
--
-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Honza.Vicherek.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2002-08-09 21:37:52 | Re: How to update record in a specified order |
Previous Message | Jean-Luc Lachance | 2002-08-09 21:36:07 | Re: SQL syntax |