From: | "Harald Armin Massa" <pgsql(dot)16(dot)ghum(at)spamgourmet(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Doing sth. like oracles "connect by" |
Date: | 2002-11-15 21:20:40 |
Message-ID: | ar3o8b$2gs6$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
What is the most effective and elegant way to substitute the connect by
clause from oracle in postgresql?
Explanation of connect by:
with "connect by" in oracle it is possible to formulate queries returning
whole hierarchies.
Example:
TablePersonal
ID IdOfChef Name PositionLevel
1 2 Karlchen 2
2 3 Melanie 3
3 4 Katja 4
4 5 Simon 5
5 NULL Miriam 6
select * from TablePersonal start with id=1 connect by prev.idofchef=id
where PositionLevel < 6
selects the total hierarchie above karlchen ... up to Simon.
select * from TablePersonal start with id=3 connect by prev.id=idofchef
gets Katja and all her downlinks (Katja, Melanie, Karlchen)
the ideas I can think are:
a) join with a a limited number of hierarchie-levels and perform well.
b) program a function "is_downlink_of(id1, id2) returns boolean" - and
check this function for every row in the table
c) (the same as c but with "is_uplink_of(id1,id2)")
Who has an idea which does not have the high processing costs of b and c and
not the limitation of a)
Thanks for your thinking
Harald
From | Date | Subject | |
---|---|---|---|
Next Message | Lee Crampton | 2002-11-15 22:09:57 | Re: Error in SELECT clause with UPPER function |
Previous Message | Robert Treat | 2002-11-15 19:01:48 | Re: Postgres Support |