Doing sth. like oracles "connect by"

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

Responses

Browse pgsql-general by date

  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