connectby questions

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.

Responses

Browse pgsql-sql by date

  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