Attached is an update to contrib/tablefunc. It introduces a new

function, connectby(), which can serve as a reference implementation for

the changes made in the last few days -- namely the ability of a
function to return an entire tuplestore, and the ability of a function
to make use of the query provided "expected" tuple description.

Description:

   connectby(text relname, text keyid_fld, text parent_keyid_fld,
     text start_with, int max_depth [, text branch_delim])
   - returns keyid, parent_keyid, level, and an optional branch string
   - requires anonymous composite type syntax in the FROM clause. See
     the instructions in the documentation below.

Joe Conway

1 files changed, 41 insertions, 5 deletions

diff --git a/contrib/tablefunc/tablefunc-test.sql b/contrib/tablefunc/tablefunc-test.sql
index e1e0a7c89e4..ab69e15497e 100644
--- a/contrib/tablefunc/tablefunc-test.sql
+++ b/contrib/tablefunc/tablefunc-test.sql
@@ -1,9 +1,4 @@
 --
--- show_all_settings()
---
-SELECT * FROM show_all_settings();
-
---
 -- normal_rand()
 --
 SELECT * FROM normal_rand(100, 250, 5, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
@@ -47,3 +42,44 @@ select * from crosstab4('select rowid, attribute, value from ct where rowclass =
 select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 2) as c(rowid text, att1 text, att2 text);
 select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as c(rowid text, att1 text, att2 text, att3 text);
 select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as c(rowid text, att1 text, att2 text, att3 text, att4 text);
+
+-- test connectby with text based hierarchy
+DROP TABLE connectby_tree;
+CREATE TABLE connectby_tree(keyid text, parent_keyid text);
+
+INSERT INTO connectby_tree VALUES('row1',NULL);
+INSERT INTO connectby_tree VALUES('row2','row1');
+INSERT INTO connectby_tree VALUES('row3','row1');
+INSERT INTO connectby_tree VALUES('row4','row2');
+INSERT INTO connectby_tree VALUES('row5','row2');
+INSERT INTO connectby_tree VALUES('row6','row4');
+INSERT INTO connectby_tree VALUES('row7','row3');
+INSERT INTO connectby_tree VALUES('row8','row6');
+INSERT INTO connectby_tree VALUES('row9','row5');
+
+-- with branch
+SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
+
+-- without branch
+SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
+
+-- test connectby with int based hierarchy
+DROP TABLE connectby_tree;
+CREATE TABLE connectby_tree(keyid int, parent_keyid int);
+
+INSERT INTO connectby_tree VALUES(1,NULL);
+INSERT INTO connectby_tree VALUES(2,1);
+INSERT INTO connectby_tree VALUES(3,1);
+INSERT INTO connectby_tree VALUES(4,2);
+INSERT INTO connectby_tree VALUES(5,2);
+INSERT INTO connectby_tree VALUES(6,4);
+INSERT INTO connectby_tree VALUES(7,3);
+INSERT INTO connectby_tree VALUES(8,6);
+INSERT INTO connectby_tree VALUES(9,5);
+
+-- with branch
+SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
+
+-- without branch
+SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
+