Re: connectby(... pos_of_sibling) - Mailing list pgsql-novice

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