Thread: 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