From: | Dan Langille <dan(at)langille(dot)org> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | connectby questions |
Date: | 2002-11-22 06:18:25 |
Message-ID: | 20021122011731.L99940-100000@m20.unixathome.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Thrasher | 2002-11-22 09:11:09 | Re: Date trunc in UTC |
Previous Message | Rudi Starcevic | 2002-11-21 23:25:07 | Dropping Ref. Integrity |