Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 809D29FA3AB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 30 Sep 2006 19:29:57 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 98219-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 30 Sep 2006 22:29:49 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mx3.ipf.is (man.ipf.is [85.197.192.163])
	by postgresql.org (Postfix) with ESMTP id 368449FA16B
	for <pgsql-sql@postgresql.org>; Sat, 30 Sep 2006 19:29:49 -0300 (ADT)
Received: (qmail 13024 invoked by uid 1010); 30 Sep 2006 22:27:53 +0000
Received: from 88.149.106.19 by man (envelope-from <gnari@hive.is>,
	uid 1008) with qmail-scanner-1.25-st-qms
	(clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms.
	Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):.
	Processed in 0.664877 secs); 30 Sep 2006 22:27:53 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via man
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):.
	Processed in 0.664877 secs Process 13017)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx3.ipf.is with SMTP; 30 Sep 2006 22:27:53 +0000
Subject: Re: tree-structured query
From: Ragnar <gnari@hive.is>
To: chester c young <chestercyoung@yahoo.com>
Cc: sql pgsql <pgsql-sql@postgresql.org>
In-Reply-To: <20060929220015.40971.qmail@web54309.mail.yahoo.com>
References: <20060929220015.40971.qmail@web54309.mail.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Sat, 30 Sep 2006 22:29:53 +0000
Message-Id: <1159655393.9076.13.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200609/178
X-Sequence-Number: 26389

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