F.36. tablefunc · PostgreSQL 中文文档 9.3

# F.36\. tablefunc `tablefun` 扩展包括了返回表记录(即:多行)的一系列函数 . 这些函数在数据记录的处理和运用C函数返回多行记录中是非常有用的 ## F.36.1\. 函数列表 [Table F-28](#calibre_link-2310) 列出了`tablefunc` 扩展提供的函数 **Table F-28\. `tablefunc`函数** | 函数 | 返回值 | 描述 | | --- | --- | --- | | `normal_rand(int numvals, float8 mean, float8 stddev)` | `setof float8` | 返回一系列正态分布的随机值 | | `crosstab(text sql)` | `setof record` | 返回一个行名加上N列数值的二维表，其中的N由查询产生的结果确定。 | | `crosstab`_N_`(text sql)` | `setof table_crosstab_``_N_` | 返回一个行名加上N列数值的二维表 `crosstab2`, `crosstab3`, and `crosstab4` 是系统定义的, 你可以按照下面描述的定义 `crosstab`_N_`` | | `crosstab(text source_sql, text category_sql)` | `setof record` | 返回一个二维表列值由第二个查询指定 | | `crosstab(text sql, int N)` | `setof record` | `crosstab(text)`的废弃版本. `_N_` 被忽略,因为列值的行数由查询决定 | | `connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])` | `setof record` | 返回一个分层的树状结构 | ### F.36.1.1\. `normal_rand` ``` normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8 ``` normal函数返回一系列正态分布的随机值(高斯分布) numvals是这个函数返回值的数目,mean时正态分布的平均值,stddev是正态分布的方差例如:这个查询返回1000个均值为5,方差为3的值 ``` test=# SELECT * FROM normal_rand(1000, 5, 3); normal_rand ---------------------- 1.56556322244898 9.10040991424657 5.36957140345079 -0.369151492880995 0.283600703686639 . . . 4.82992125404908 9.71308014517282 2.49639286969028 (1000 rows) ``` ### F.36.1.2\. `crosstab(text)` ``` crosstab(text sql) crosstab(text sql, int N) ``` crosstab函数返回一个二维表,数据在这个表里通过横向而非纵向列出,例如: 我们已有数据如下: ``` row1 val11 row1 val12 row1 val13 ... row2 val21 row2 val22 row2 val23 ... ``` 而我们想要按如下输出: ``` row1 val11 val12 val13 ... row2 val21 val22 val23 ... ... ``` crosstab函数使用一个SQL查询作为参数,返回一个给定格式的二维表 sql参数用来产生一组数据源,这个语句必须返回1个row_name列,一个category列,一个value列. N是一个废弃的参数, 例如,一个查询需要产生如下形式的数据: ``` row_name cat value ----------+-------+------- row1 cat1 val1 row1 cat2 val2 row1 cat3 val3 row1 cat4 val4 row2 cat1 val5 row2 cat2 val6 row2 cat3 val7 row2 cat4 val8 ``` crosstab函数返回一系列记录,输出列的实际名字和类型必须定义在FROM字句中,例如: ``` SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text); ``` This example produces a set something like: ``` <== value columns ==> row_name category_1 category_2 ----------+------------+------------ row1 val1 val2 row2 val5 val6 ``` FROM字句必须定义一个row_name(和你查询返回第一列具有相同数据类型)列和一个和N个value列 (和查询返回第三列的类型匹配). 由你来定义输出列的名字和数量. crosstab函数对于具有相同row_name的一组数据返回一行.它使用这些行从左到右填充输出到二维表.如果这组数据少于输出的列数,则使用null填充, 如果有多余的行数,则额外的输入将被忽略 SQL查询应该一直指定ORDER BY1,2来确保输入的数据时有序的,即, 具有相同row_name的行是连续的并且在一行的输出中是有序的.注意crosstab 不关注第二列查询的结果;它只是用来保证第三列的值在输出中的排序. 这是一个完整的例子: ``` CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8'); SELECT * FROM crosstab( 'select rowid, attribute, value from ct where attribute = ''att2'' or attribute = ''att3'' order by 1,2') AS ct(row_name text, category_1 text, category_2 text, category_3 text); row_name | category_1 | category_2 | category_3 ----------+------------+------------+------------ test1 | val2 | val3 | test2 | val6 | val7 | (2 rows) ``` 可以通过定义一个crosstab函数去定义任意类型,数量的列,来避免每次在FROM 字句中制定输出列的值和类型.这将在下一节中讲解.另外一种方式是在定义视图的 FROM字句中制定. ### F.36.1.3\. `crosstab`_N_`(text)` ``` crosstab_N_(text sql) ``` crosstabN函数是crosstab函数封装的一个例子,在使用SELECT查询中,你可以不指定列数和类型.tablefunc扩展定义了crosstab2,crosstab3,crosstab4函数中列的类型数量. ``` CREATE TYPE tablefunc_crosstab_N AS ( row_name TEXT, category_1 TEXT, category_2 TEXT, . . . category_N TEXT ); ``` 当输入查询返回text类型的row_name并且你有2,3,4列输出值时你可以直接使用这些函数.其他情况需要像上面的定义crosstab函数一样. 例如,上面的例子也可以像下面一样运行. ``` SELECT * FROM crosstab3( 'select rowid, attribute, value from ct where attribute = ''att2'' or attribute = ''att3'' order by 1,2'); ``` 这些函数大部分只是为了举例说明.你可以按照crosstab函数定义你需要的返回类型的函数.这里有两种方法来实现: * 定义一个复合类型的输出列类似于contrib/tablefunc/tablefunc--1.0.sql中的例子. 按照crosstab函数定义的方式,定义一个唯一函数名来接收一个text类型的参数,返回setof your_type_name. 例如,你的数据源返回一个text类型的row_name,values字段为float8类型,并且你需要5列: ``` CREATE TYPE my_crosstab_float8_5_cols AS ( my_row_name text, my_category_1 float8, my_category_2 float8, my_category_3 float8, my_category_4 float8, my_category_5 float8 ); CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text) RETURNS setof my_crosstab_float8_5_cols AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; ``` * 使用OUT参数定义返回值类型.上面的例子可以像下面定义: ``` CREATE OR REPLACE FUNCTION crosstab_float8_5_cols( IN text, OUT my_row_name text, OUT my_category_1 float8, OUT my_category_2 float8, OUT my_category_3 float8, OUT my_category_4 float8, OUT my_category_5 float8) RETURNS setof record AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; ``` ### F.36.1.4\. `crosstab(text, text)` ``` crosstab(text source_sql, text category_sql) ``` 单参数的crosstab函数的主要限制是处理一组记录时插入每一个值到第一个列中. 如果你想插入值到指定的数据属性中,一些数据可能没有该属性的值,这样它将不能很好的处理.两个参数的crosstab通过提供一个指定的属性列表能较好的处理该类问题. source_sql语句返回一组数据源.这个语句返回一个row_name列,一个category列 ,和一个value列,它也可能有一个或多个extra列.row_name必须是第一列.category和 value列必须是最后两列.任何在在row_name和category中间的列被认为时extra列. 有相同row_name的extra列被认为是相同的. 例如,source_sql可能返回下面的一系列记录: ``` SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1; row_name extra_col cat value ----------+------------+-----+--------- row1 extra1 cat1 val1 row1 extra1 cat2 val2 row1 extra1 cat4 val4 row2 extra2 cat1 val5 row2 extra2 cat2 val6 row2 extra2 cat3 val7 row2 extra2 cat4 val8 ``` category_sql返回categories记录.这个语句只能返回一列.它必须最少返回一行,否则会报错.它也不能返回重复的值,否则会报错.category_sql必须如下面: ``` SELECT DISTINCT cat FROM foo ORDER BY 1; cat ------- cat1 cat2 cat3 cat4 ``` crosstab函数返回一系列记录,列的名字和类型必须定义在SELECT中的FROM字句中. ``` SELECT * FROM crosstab('...', '...') AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text); ``` 这将返回如下记录: ``` <== value columns ==> row_name extra cat1 cat2 cat3 cat4 ---------+-------+------+------+------+------ row1 extra1 val1 val2 val4 row2 extra2 val5 val6 val7 val8 ``` FROM字句必须定义类型匹配的适当数量的列.如果在source_sql中返回N列,前面的N-2 列必须匹配前N-2列.剩下的列必须和source_sql返回的列类型匹配,并且返回的行数需要和category_sql返回的相同. crosstab函数对于具有相同row_name的一组连续记录返回一行输出.复制这组记录的第一行到row_name和extra列.记录中匹配category的值填充到value列中. 如果一行的category不能匹配任何category_sql返回的结果,它的值将被忽略. 如果输入行中的category不匹配任何category_sql的返回结果,这一个输出列将被填充为null. 实际上source_sql一直使用ORDER BY 1去确保具有相同row_name的行连续.然而, categories排序不是必须.它实际上时为了保证匹配category_sql的记录有序. 这有两个完整的例子: ``` create table sales(year int, month int, qty int); insert into sales values(2007, 1, 1000); insert into sales values(2007, 2, 1500); insert into sales values(2007, 7, 500); insert into sales values(2007, 11, 1500); insert into sales values(2007, 12, 2000); insert into sales values(2008, 1, 1000); select * from crosstab( 'select year, month, qty from sales order by 1', 'select m from generate_series(1,12) m' ) as ( year int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int ); year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec ------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------ 2007 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000 2008 | 1000 | | | | | | | | | | | (2 rows) ``` ``` CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text); INSERT INTO cth VALUES('test1','01 March 2003','temperature','42'); INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS'); INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987'); INSERT INTO cth VALUES('test2','02 March 2003','temperature','53'); INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL'); INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003'); INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234'); SELECT * FROM crosstab ( 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 'SELECT DISTINCT attribute FROM cth ORDER BY 1' ) AS ( rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8 ); rowid | rowdt | temperature | test_result | test_startdate | volts -------+--------------------------+-------------+-------------+--------------------------+-------- test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 (2 rows) ``` 你能预定义一个函数来避免在每一个查询中都指定输出列的名字和类型. 查看前面部分的例子.构成这种形式的crosstab被命名为crosstab_hash. ### F.36.1.5\. `connectby` ``` connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ]) ``` connectby函数返回一个表的分层显示.这个表必须有一个键值去唯一的标识一行, 并且有一个父节点键值去关联每一行.connectby能从任意一行列出一个子树 [Table F-29](#calibre_link-2311) explains the parameters. **Table F-29\. `connectby` 参数** | 参数 | 描述 | | --- | --- | | `relname` | 源表的名字 | | `keyid_fld` | 键值的名字 | | `parent_keyid_fld` | 父节点键值的名字 | | `orderby_fld` | 排序兄弟的字段名字 | | `start_with` | 键值开始值 | | `max_depth` | 最大深度,0表示深度无限 | | `branch_delim` | 每个分支的独立键值 | 键值和父节点键值可以是相同的任意类型,注意start_with必须是一个字符串,无论key的类型. connectby函数返回一系列记录,所以输出列的名字和类型必须定义在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); ``` 输出的前两列做为现在行的键值和父节点的键值;他们必须和表的键值类型匹配.第三列为节点在树中的深度必须为整形.如果给出branch_delim参数,下一列是一个text类型的分支列,最后如果给出orderby_fld参数最后一列是一个整形的自增值. branch列给出键值到达当前行的路径.键值被branch_delim字段给出的字符分割开. 如果不想显示分支,在每一个输出列表中忽略branch_delim和branch参数. 如果想要排序同一个父节点的兄弟节点,指定orderby_fld参数来指定需要排序兄弟节点的字段.如果orderby_fld被指定,输出列一定包括一个整形自增的列. 表和字段值的参数被connectby函数自动的复制到查询语句中.因此如果名字包含特殊字符或者混合字段请使用"",你可能也需要使用模式来限定表名. 在大的表中,除非在父节点的键值上有一个索引,否则效率将会很差. 在键值中不出现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); -- with branch, without orderby_fld (order of results is not guaranteed) 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) -- without branch, without orderby_fld (order of results is not guaranteed) 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) -- with branch, with orderby_fld (notice that row5 comes before 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) -- without branch, with orderby_fld (notice that row5 comes before 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) ``` ## F.36.2\. Author Joe Conway