Обсуждение: Doing sth. like oracles "connect by"

Поиск
Список
Период
Сортировка

Doing sth. like oracles "connect by"

От
"Harald Armin Massa"
Дата:
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

Re: Doing sth. like oracles "connect by"

От
Joe Conway
Дата:
Harald Armin Massa wrote:
> 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.
>

If you can try 7.3 (finishing beta, soon to be released), see
contrib/tablefunc for a function called connectby(). There is also a different
approach to the problem in contrib/ltree.

If you cannot use 7.3, there have been past discussions wrt implementing tree
structure -- search the mail archives. I *think* contrib/ltree might be
available backported to 7.2 -- yeah, from the README:

   "(version for 7.2 version is available from
    http://www.sai.msu.su/~megera/postgres/gist/ltree/ltree-7.2.tar.gz)"

HTH,

Joe