contrib/tablefunc/connectby question
test=# \d menu
id | integer | not null default
nextval('public.menu_id_seq'::text)
parent_id | integer |
data | text
I write query:
SELECT * FROM connectby('menu', 'id', 'parent_id', 1, 0)
AS t(id integer, parent_id integer, level integer);
and get only id, parent_id, level fields!
how am I able to get data field?
(only this way: SELECT id_, parent_id_, level, data FROM
connectby('menu', 'id', 'parent_id', 8, 0) AS t(id_ integer, parent_id_
integer, level integer), menu where menu.id = id_; ???)
--
WBR, sector119
sector119@mail.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