Thread: connectby(... pos_of_sibling)

connectby(... pos_of_sibling)

From
Nabil Sayegh
Date:
Hi all,

I have a question about connectby():
Is it possible to have the siblings sorted ?

pos gives the order of the siblings:

--------------------------8<-------------------------
CREATE TABLE nav (
    id_nav serial primary key,
    id2_nav integer references nav,
    nav text not null,
    pos integer not null
);

COPY nav (id_nav, id2_nav, nav, pos) FROM stdin;
1       \N      World   0
2       1       Top     1
3       1       Left    2
4       1       Support 0
5       2       Home    0
6       2       News    1
7       4       Contact 0
8       4       Search  1
9       4       Sitemap 2
10      4       Imprint 3
\.

SELECT pg_catalog.setval ('nav_id_nav_seq', 10, true);
-------------------------8<---------------------------

If I join the resulting tree with another table, the order could be
destroyed again (?)
So I think there should also be a temporary SEQ that can be used in the
ORDER BY clause at the end.

Any idea ?
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de