From 6aa4482f2f520bb06fa1016eef3960f80bf88bdf Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Mon, 2 Sep 2002 05:44:05 +0000 Subject: 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 --- contrib/tablefunc/tablefunc-test.sql | 46 ++++++++++++++++++++++++++++++++---- 1 file changed, 41 insertions(+), 5 deletions(-) (limited to 'contrib/tablefunc/tablefunc-test.sql') 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,8 +1,3 @@ --- --- show_all_settings() --- -SELECT * FROM show_all_settings(); - -- -- normal_rand() -- @@ -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); + -- cgit v1.2.3