Re: tablefunc functions in postgresql

Dear Mr. Conway,

I've noticed that you're the author of the tablefunc functions in the
postgresql distribution. I've come across a problem that I think is a bug.
I realize that you're of course under no obligation to support this
software, so I am just writing turn your attention to the problem - if
indeed it is a problem :)

I've go the following table defined:

create table domains (
id int not null primary key default nextval('domain_id_seq'),
name varchar(64) not null,
description text,
owner_id int
) without oids;

And for testing it contains the following:

ipsmart=> select * from domains;
id | name | description |
owner_id
----+---------------------+------------------------------------------+------
----
0 | justit.ws | The overall owner of the IPSmart product |
1 | sp1.justit.ws | Domain 1 |
0
2 | sp2.justit.ws | Domain 2 |
0
3 | test1.sp1.justit.ws | Sub Domain 1 |
1
4 | test2.sp1.justit.ws | Sub Domain 2 |
1

The owner_id column referenfes the id column of the same table.

Now - I need to traverse this table from a certain level (and in production
it will become rather deep).

I've got two problems - one just an annoying "feature" of the connectby
function and the other I think is a bug. Let's start with the bug :)
Traversing from node '0' is ok:

ipsmart=> select * from connectby('domains', 'id', 'owner_id', '0', 0) as
t(id int, owner_id int, level int);
id | owner_id | level
----+----------+-------
0 | | 0
1 | 0 | 1
3 | 1 | 2
4 | 1 | 2
2 | 0 | 1
(5 rows)

No problem there. Traversing from node 1 also works fine (results are 1, 3
and 4). But traversing from node 2 - nothing is returned.

ipsmart=> select * from connectby('domains', 'id', 'owner_id', '2', 0) as
t(id int, owner_id int, level int);
id | owner_id | level
----+----------+-------
(0 rows)

This is kind of weird since in the first two examples the root node was
clearly returned as well. Is this a feature or a bug?

The other problem is not really a bug but rather a slight irritation :)
Originally I had the 'owner_id" defined as "not null'. I let the root refer
to itself as parent and added the references constraint after I inserted the
first record. This way I could guarantee that the parent was always
defined. Unfortunately your "connectby" function wouldn't accept this and
it lead to an infinite recurse. Wouldn't it be reasonable easy to let your
function stop if the node was referring to itself?

Anyway - as mentioned earlier - I don't really expect that you've got time
to look at this (although I would of course appreciate it a lot if you had).
Even if you don't have time - I can live with the minor problems unless
PostgreSQL get a real "connect by prior" construct :)

Regards,

Lars...

--
Lars Boegild Thomsen
Technical Director
JustIT Sdn. Bhd.
Cell Phone (MY): +60 (16) 323 1999
ICQ: 6478559
Yahoo Chat: lars_boegild_thomsen@yahoo.com
MSN Chat: lars_boegild_thomsen@hotmail.com
http://www.justit.ws