I just installed 7.3rc1 and added contrib/tablefunc. I am able to get the
example in the README document to work. I am having trouble understanding
how to get my real data to behave.
The table is:
freshports=# \d element Table "public.element" Column | Type |
Modifiers
---------------------+--------------+--------------------------------------------------id | integer
| not null default
nextval('element_id_seq'::text)name | text | not nullparent_id | integer
|directory_file_flag| character(1) | not nullstatus | character(1) | not null
I have been able to get simple examples to work:
freshports=# select id, parent_id from connectby('element', 'id',
'parent_id', '104503', 0) as t(id int, parent_id int, level int); id | parent_id
--------+-----------104503 |104504 | 104503104505 | 104503
(3 rows)
Why does level not appear here? I see a similar problem with this query:
freshports=# select id, parent_id from connectby('element', 'id',
'parent_id', '104503', 0, '/') as t(id int, parent_id int, level int,
branch text); id | parent_id
--------+-----------104503 |104504 | 104503104505 | 104503
(3 rows)
Here is the actual data for the above nodes:
freshports=# select * from element where id in (104503, 104504, 104505); id | name | parent_id |
directory_file_flag| status
--------+--------------+-----------+---------------------+--------104503 | multimedia | 77344 | D
| A104504 | Makefile | 104503 | F | A104505 | chapter.sgml | 104503 | F
|A
(3 rows)
What I would like to include in the output is all of the above fields.
But I can't seem to get that to work:
freshports=# select id, parent_id, name from connectby('element', 'id',
'parent_id', '104503', 0, '/') ast(id int, parent_id int, level int, branch text, name text);
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
I was able to do this with a view:
freshports=# create view simple_element as select id, parent_id from
element;
CREATE VIEW
freshports=# select * from connectby('simple_element', 'id', 'parent_id',
'104503', 0, '/') as t(id int,
parent_id int, level int, branch text); id | parent_id | level | branch
--------+-----------+-------+---------------104503 | | 0 | 104503104504 | 104503 | 1 |
104503/104504104505| 104503 | 1 | 104503/104505
(3 rows)
Whis is expected given what I see in the README.
But there doesn't seem to be any way to get the name field out:
freshports=# drop view simple_element;
DROP VIEW
freshports=# create view simple_element as select id, parent_id, name from
element;
CREATE VIEW
freshports=# select * from connectby('simple_element', 'id', 'parent_id',
'104503', 0, '/') as t(id int,
parent_id int, level int, branch text); id | parent_id | level | branch
--------+-----------+-------+---------------104503 | | 0 | 104503104504 | 104503 | 1 |
104503/104504104505| 104503 | 1 | 104503/104505
(3 rows)
freshports=# select * from connectby('simple_element', 'id', 'parent_id',
'104503', 0, '/') as t(id int, parent_id int, level int, branch text, name
text);
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
freshports=#
I hope it's just that it's late and I'm missing something. Cheers.