Doing sth. like oracles "connect by"

Поиск
Список
Период
Сортировка
От Harald Armin Massa
Тема Doing sth. like oracles "connect by"
Дата
Msg-id ar3o8b$2gs6$1@news.hub.org
обсуждение исходный текст
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: "Josh Berkus"
Дата:
Сообщение: Re: More than 16 args to postgres
Следующее
От: "Lee Crampton"
Дата:
Сообщение: Re: Error in SELECT clause with UPPER function