connectby(text relname, text keyid_fld, text parent_keyid_fld
[, text orderby_fld ], text start_with, int max_depth
[, text branch_delim ])
connectby函数产生存储在一个表中的分级数据的显示。该表必须具有用以唯一标识行的键字段,以及每一行引用的父键字段(如果有)。connectby能显示从劝拿庞蜗菲教ㄗ⒉嵬锯行开始向下的子树。
表?9.2.?connectby参数
参数 | 描述 |
---|---|
relname | 源关系的名称。 |
keyid_fld | 键的名称。 |
parent_keyid_fld | 父键的名称。 |
orderby_fld | 用于排序同级的键名称(可选)。 |
start_with | 起始行的键值。 |
max_depth | 要向下的最大深度,零表示无限深度。 |
branch_delim | 在分支输出中用于分隔键值的字符串(可选)。 |
键和父键可以是劝拿庞蜗菲教ㄗ⒉嵬锯数据类型,但是必须是同一类型。注意start_with值必须作为一个文本串被输入,而不管键域的类型如何。
connectby函数被声明为返回setof record,因此输出列的实际名称和类型就必须在调用的SELECT语句的FROM子句中被定义,例如:
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int);
前两个输出列是被用于当前行的键和其父行的键,它们必须匹配该表的键的类型。第三个输出列是该树中的深度,并且必须是类型integer。如果给定了一个branch_delim参数,下一个输出列就是分支显示并且必须是类型text。最后,如果给出了一个orderby_fld参数,最后一个输出列是一个序号,并且必须是类型integer。
“branch”输出列显示了用于到达当前行的由键构成的路径。其中的键用指定的branch_delim字符串分隔开。如果不需要分支显示,可以在输出列列表中忽略branch_delim参数和分支列。
如果同一父键域的子键域之间的顺序很重要,可以使用orderby_fld参数指定用域对兄弟键域进行排序。这个域可以是任何可排序数据类型。当且仅当orderby_fld被指定时,输出列列表必须包括一个最终的整数序号列。
表示表和列名的参数会被原样复制到connectby内部生成的SQL查询中。因此,如果名称是大小写混合或者包含特殊字符,应包括双引号。表名需要用模式限定。
在大型的表中,在父键域上需要有索引,否则性能会很差。
branch_delim字符串不能出现在任何键值中,否则connectby可能会错误地报告一个无限递归错误。注意如果没有提供branch_delim,将用默认值(~)来进行递归检测。
示例:
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);
-- 带有分支,但没有orderby_fld(不保证结果的顺序)。
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch -------+--------------+-------+--------------------- row2 | | 0 | row2 row4 | row2 | 1 | row2~row4 row6 | row4 | 2 | row2~row4~row6 row8 | row6 | 3 | row2~row4~row6~row8 row5 | row2 | 1 | row2~row5 row9 | row5 | 2 | row2~row5~row9 (6 rows)
-- 没有分支,也没有orderby_fld(不保证结果的顺序)。
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
keyid | parent_keyid | level -------+--------------+------- row2 | | 0 row4 | row2 | 1 row6 | row4 | 2 row8 | row6 | 3 row5 | row2 | 1 row9 | row5 | 2 (6 rows)
-- 有分支,有orderby_fld(注意row5在row4前面)。
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int);
keyid | parent_keyid | level | branch | pos -------+--------------+-------+---------------------+----- row2 | | 0 | row2 | 1 row5 | row2 | 1 | row2~row5 | 2 row9 | row5 | 2 | row2~row5~row9 | 3 row4 | row2 | 1 | row2~row4 | 4 row6 | row4 | 2 | row2~row4~row6 | 5 row8 | row6 | 3 | row2~row4~row6~row8 | 6 (6 rows)
-- 没有分支,有orderby_fld(注意row5在row4前面)。
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int);
keyid | parent_keyid | level | pos -------+--------------+-------+----- row2 | | 0 | 1 row5 | row2 | 1 | 2 row9 | row5 | 2 | 3 row4 | row2 | 1 | 4 row6 | row4 | 2 | 5 row8 | row6 | 3 | 6 (6 rows)