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 null
parent_id | integer |
directory_file_flag | character(1) | not null
status | 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 | 104503
104505 | 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 | 104503
104505 | 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 | A
104504 | Makefile | 104503 | F | A
104505 | 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, '/') 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
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 | 104503
104504 | 104503 | 1 | 104503/104504
104505 | 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 | 104503
104504 | 104503 | 1 | 104503/104504
104505 | 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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
