Re: contrib/tablefunc/connectby question

sector119(at)mail(dot)ru wrote:
> how am I able to get data field?

create table menu (id integer, parent_id integer, data text);
insert into menu values(1,null,'root');
insert into menu values(2,1,'menu 2');
insert into menu values(3,1,'menu 3');
insert into menu values(4,2,'menu 4');
insert into menu values(5,2,'menu 5');
insert into menu values(6,4,'menu 6');
insert into menu values(7,3,'menu 7');
insert into menu values(8,6,'menu 8');
insert into menu values(9,5,'menu 9');

regression=# SELECT c.*, m.data FROM connectby('menu', 'id',
'parent_id', '1', 0, '~') AS c(id int, parent_id int, level int, branch
text), menu m WHERE m.id = c.id;
id | parent_id | level | branch | data
----+-----------+-------+-----------+--------
1 | | 0 | 1 | root
2 | 1 | 1 | 1~2 | menu 2
3 | 1 | 1 | 1~3 | menu 3
4 | 2 | 2 | 1~2~4 | menu 4
5 | 2 | 2 | 1~2~5 | menu 5
6 | 4 | 3 | 1~2~4~6 | menu 6
7 | 3 | 2 | 1~3~7 | menu 7
8 | 6 | 4 | 1~2~4~6~8 | menu 8
9 | 5 | 3 | 1~2~5~9 | menu 9
(9 rows)

HTH,

Joe