Re: tablefunc functions in postgresql

Attached is a patch for contrib/tablefunc. It fixes two issues raised by
Lars Boegild Thomsen (full email below) and also corrects the regression
expected output for a recent backend message adjustment. Please apply.

Thanks,

Joe

Lars Boegild Thomsen wrote:
> 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(at)yahoo(dot)com
> MSN Chat: lars_boegild_thomsen(at)hotmail(dot)com
> http://www.justit.ws
>