Date: Fri, 9 Aug 2002 17:36:35 -0400 (EDT)
From: h012@ied.com
To: pgsql-sql@postgresql.org
Subject: retrieving all rows from a "tree" in one select - how ?
Message-ID: <no.Yo.N.nN.0208091650190.2280-100000@business.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


 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/