Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6aa4482

Browse files
committed
Attached is an update to contrib/tablefunc. It introduces a new
function, connectby(), which can serve as a reference implementation forthe changes made in the last few days -- namely the ability of afunction to return an entire tuplestore, and the ability of a functionto 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 parent9fd842c commit6aa4482

File tree

5 files changed

+602
-12
lines changed

5 files changed

+602
-12
lines changed

‎contrib/tablefunc/README.tablefunc

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -60,6 +60,12 @@ Installation:
6060
- requires anonymous composite type syntax in the FROM clause. See
6161
the instructions in the documentation below.
6262

63+
connectby(text relname, text keyid_fld, text parent_keyid_fld,
64+
text start_with, int max_depth [, text branch_delim])
65+
- returns keyid, parent_keyid, level, and an optional branch string
66+
- requires anonymous composite type syntax in the FROM clause. See
67+
the instructions in the documentation below.
68+
6369
Documentation
6470
==================================================================
6571
Name
@@ -324,6 +330,109 @@ AS ct(row_name text, category_1 text, category_2 text, category_3 text);
324330
test2 | val6 | val7 |
325331
(2 rows)
326332

333+
==================================================================
334+
Name
335+
336+
connectby(text, text, text, text, int[, text]) - returns a set
337+
representing a hierarchy (tree structure)
338+
339+
Synopsis
340+
341+
connectby(text relname, text keyid_fld, text parent_keyid_fld,
342+
text start_with, int max_depth [, text branch_delim])
343+
344+
Inputs
345+
346+
relname
347+
348+
Name of the source relation
349+
350+
keyid_fld
351+
352+
Name of the key field
353+
354+
parent_keyid_fld
355+
356+
Name of the key_parent field
357+
358+
start_with
359+
360+
root value of the tree input as a text value regardless of keyid_fld type
361+
362+
max_depth
363+
364+
zero (0) for unlimited depth, otherwise restrict level to this depth
365+
366+
branch_delim
367+
368+
if optional branch value is desired, this string is used as the delimiter
369+
370+
Outputs
371+
372+
Returns setof record, which must defined with a column definition
373+
in the FROM clause of the SELECT statement, e.g.:
374+
375+
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
376+
AS t(keyid text, parent_keyid text, level int, branch text);
377+
378+
- or -
379+
380+
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
381+
AS t(keyid text, parent_keyid text, level int);
382+
383+
Notes
384+
385+
1. keyid and parent_keyid must be the same data type
386+
387+
2. The column definition *must* include a third column of type INT4 for
388+
the level value output
389+
390+
3. If the branch field is not desired, omit both the branch_delim input
391+
parameter *and* the branch field in the query column definition
392+
393+
4. If the branch field is desired, it must be the forth column in the query
394+
column definition, and it must be type TEXT
395+
396+
Example usage
397+
398+
CREATE TABLE connectby_tree(keyid text, parent_keyid text);
399+
400+
INSERT INTO connectby_tree VALUES('row1',NULL);
401+
INSERT INTO connectby_tree VALUES('row2','row1');
402+
INSERT INTO connectby_tree VALUES('row3','row1');
403+
INSERT INTO connectby_tree VALUES('row4','row2');
404+
INSERT INTO connectby_tree VALUES('row5','row2');
405+
INSERT INTO connectby_tree VALUES('row6','row4');
406+
INSERT INTO connectby_tree VALUES('row7','row3');
407+
INSERT INTO connectby_tree VALUES('row8','row6');
408+
INSERT INTO connectby_tree VALUES('row9','row5');
409+
410+
-- with branch
411+
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
412+
AS t(keyid text, parent_keyid text, level int, branch text);
413+
keyid | parent_keyid | level | branch
414+
-------+--------------+-------+---------------------
415+
row2 | | 0 | row2
416+
row4 | row2 | 1 | row2~row4
417+
row6 | row4 | 2 | row2~row4~row6
418+
row8 | row6 | 3 | row2~row4~row6~row8
419+
row5 | row2 | 1 | row2~row5
420+
row9 | row5 | 2 | row2~row5~row9
421+
(6 rows)
422+
423+
-- without branch
424+
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
425+
AS t(keyid text, parent_keyid text, level int);
426+
keyid | parent_keyid | level
427+
-------+--------------+-------
428+
row2 | | 0
429+
row4 | row2 | 1
430+
row6 | row4 | 2
431+
row8 | row6 | 3
432+
row5 | row2 | 1
433+
row9 | row5 | 2
434+
(6 rows)
435+
327436
==================================================================
328437
-- Joe Conway
329438

‎contrib/tablefunc/tablefunc-test.sql

Lines changed: 41 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,3 @@
1-
--
2-
-- show_all_settings()
3-
--
4-
SELECT*FROM show_all_settings();
5-
61
--
72
-- normal_rand()
83
--
@@ -47,3 +42,44 @@ select * from crosstab4('select rowid, attribute, value from ct where rowclass =
4742
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',2)as c(rowidtext, att1text, att2text);
4843
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',3)as c(rowidtext, att1text, att2text, att3text);
4944
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',4)as c(rowidtext, att1text, att2text, att3text, att4text);
45+
46+
-- test connectby with text based hierarchy
47+
DROPTABLE connectby_tree;
48+
CREATETABLEconnectby_tree(keyidtext, parent_keyidtext);
49+
50+
INSERT INTO connectby_treeVALUES('row1',NULL);
51+
INSERT INTO connectby_treeVALUES('row2','row1');
52+
INSERT INTO connectby_treeVALUES('row3','row1');
53+
INSERT INTO connectby_treeVALUES('row4','row2');
54+
INSERT INTO connectby_treeVALUES('row5','row2');
55+
INSERT INTO connectby_treeVALUES('row6','row4');
56+
INSERT INTO connectby_treeVALUES('row7','row3');
57+
INSERT INTO connectby_treeVALUES('row8','row6');
58+
INSERT INTO connectby_treeVALUES('row9','row5');
59+
60+
-- with branch
61+
SELECT*FROM connectby('connectby_tree','keyid','parent_keyid','row2',0,'~')AS t(keyidtext, parent_keyidtext, levelint, branchtext);
62+
63+
-- without branch
64+
SELECT*FROM connectby('connectby_tree','keyid','parent_keyid','row2',0)AS t(keyidtext, parent_keyidtext, levelint);
65+
66+
-- test connectby with int based hierarchy
67+
DROPTABLE connectby_tree;
68+
CREATETABLEconnectby_tree(keyidint, parent_keyidint);
69+
70+
INSERT INTO connectby_treeVALUES(1,NULL);
71+
INSERT INTO connectby_treeVALUES(2,1);
72+
INSERT INTO connectby_treeVALUES(3,1);
73+
INSERT INTO connectby_treeVALUES(4,2);
74+
INSERT INTO connectby_treeVALUES(5,2);
75+
INSERT INTO connectby_treeVALUES(6,4);
76+
INSERT INTO connectby_treeVALUES(7,3);
77+
INSERT INTO connectby_treeVALUES(8,6);
78+
INSERT INTO connectby_treeVALUES(9,5);
79+
80+
-- with branch
81+
SELECT*FROM connectby('connectby_tree','keyid','parent_keyid','2',0,'~')AS t(keyidint, parent_keyidint, levelint, branchtext);
82+
83+
-- without branch
84+
SELECT*FROM connectby('connectby_tree','keyid','parent_keyid','2',0)AS t(keyidint, parent_keyidint, levelint);
85+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp