Re: connectby(... pos_of_sibling)

Nabil Sayegh wrote:
> 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.
>

The only way to do what you want (if I understand correctly) currently
is to use padded keys and then sort by branch. Here's a bit of a
workaround that might do the trick for you:

create or replace function pad_id(int,int) returns text as 'select
repeat(''0'', $2 - length($1::text)) || $1' language 'sql';

create view nav_vw as select pad_id(id_nav,4) as id_nav,
pad_id(id2_nav,4) as id2_nav, nav, pos from nav;

select ss.id_nav,ss.id2_nav,ss.level,ss.branch,n.nav,n.pos from nav_vw
n, (select id_nav,id2_nav,level,branch from
connectby('nav_vw','id_nav','id2_nav','0001',0,'~') as (id_nav text,
id2_nav text, level int, branch text)) as ss where n.id_nav = ss.id_nav
order by ss.branch;
id_nav | id2_nav | level | branch | nav | pos
--------+---------+-------+----------------+---------+-----
0001 | | 0 | 0001 | World | 0
0002 | 0001 | 1 | 0001~0002 | Top | 1
0005 | 0002 | 2 | 0001~0002~0005 | Home | 0
0006 | 0002 | 2 | 0001~0002~0006 | News | 1
0003 | 0001 | 1 | 0001~0003 | Left | 2
0004 | 0001 | 1 | 0001~0004 | Support | 0
0007 | 0004 | 2 | 0001~0004~0007 | Contact | 0
0008 | 0004 | 2 | 0001~0004~0008 | Search | 1
0009 | 0004 | 2 | 0001~0004~0009 | Sitemap | 2
0010 | 0004 | 2 | 0001~0004~0010 | Imprint | 3
(10 rows)

In 7.4 there may be a slightly better workaround (if a submitted patch
gets accepted). You can convert branch into an array of integers, and
order by that:

select ss.id_nav, ss.id2_nav, ss.level,
string_to_array(ss.branch,'~')::int[] as branch, n.nav,n.pos from nav n,
(select id_nav,id2_nav,level,branch from
connectby('nav','id_nav','id2_nav','1',0,'~') as (id_nav int, id2_nav
int, level int, branch text)) as ss where n.id_nav = ss.id_nav order by
string_to_array(ss.branch,'~')::int[];
id_nav | id2_nav | level | branch | nav | pos
--------+---------+-------+----------+---------+-----
1 | | 0 | {1} | World | 0
2 | 1 | 1 | {1,2} | Top | 1
5 | 2 | 2 | {1,2,5} | Home | 0
6 | 2 | 2 | {1,2,6} | News | 1
3 | 1 | 1 | {1,3} | Left | 2
4 | 1 | 1 | {1,4} | Support | 0
7 | 4 | 2 | {1,4,7} | Contact | 0
8 | 4 | 2 | {1,4,8} | Search | 1
9 | 4 | 2 | {1,4,9} | Sitemap | 2
10 | 4 | 2 | {1,4,10} | Imprint | 3
(10 rows)

I'll think more about a row number column though. Maybe for 7.4 (but
then again, times running out and I have a few things in front of this,
so no promises).

Joe