> The previous patch fixed an infinite recursion bug in

> contrib/tablefunc/tablefunc.c:connectby. But, other unmanageable error
> seems to occur even if a table has commonplace tree data(see below).
>
> I would think the patch, ancestor check, should be
>
>   if (strstr(branch_delim || branchstr->data || branch_delim,
>                        branch_delim || current_key || branch_delim))
>
> This is my image, not a real code. However, if branchstr->data includes
> branch_delim, my image will not be perfect.

Good point. Thank you Masaru for the suggested fix.

Attached is a patch to fix the bug found by Masaru. His example now
produces:

regression=# SELECT * FROM connectby('connectby_tree', 'keyid',
'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level
int,
branch text);
  keyid | parent_keyid | level |  branch

-------+--------------+-------+----------
     11 |              |     0 | 11
     10 |           11 |     1 | 11-10
    111 |           11 |     1 | 11-111
      1 |          111 |     2 | 11-111-1
(4 rows)

While making the patch I also realized that the "no show branch" form of
the  function was not going to work very well for recursion detection.
Therefore  there is now a default branch delimiter ('~') that is used
internally, for  that case, to enable recursion detection to work. If
you need a different  delimiter for your specific data, you will have to
use the "show branch" form  of the function.

Joe Conway

1 files changed, 7 insertions, 2 deletions

diff --git a/contrib/tablefunc/README.tablefunc b/contrib/tablefunc/README.tablefunc
index fe437256d7..c1faf190b3 100644
--- a/contrib/tablefunc/README.tablefunc
+++ b/contrib/tablefunc/README.tablefunc
@@ -365,7 +365,9 @@ Inputs
 
   branch_delim
 
-    if optional branch value is desired, this string is used as the delimiter
+    If optional branch value is desired, this string is used as the delimiter.
+    When not provided, a default value of '~' is used for internal 
+    recursion detection only, and no "branch" field is returned.
 
 Outputs
 
@@ -388,7 +390,10 @@ Notes
      the level value output
 
   3. If the branch field is not desired, omit both the branch_delim input
-     parameter *and* the branch field in the query column definition
+     parameter *and* the branch field in the query column definition. Note
+     that when branch_delim is not provided, a default value of '~' is used
+     for branch_delim for internal recursion detection, even though the branch
+     field is not returned.
 
   4. If the branch field is desired, it must be the forth column in the query
      column definition, and it must be type TEXT