登录F.43. tablefunc — 返回表的函数（crosstab和其他函数）#
tablefunc模块包括返回表（即，多行）的各种函数。这些函数本身很有用，而且可以作为编写返回多行的 C 函数的示例。
此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有CREATE权限的非超级用户安装。
F.43.1. 提供的函数#
表 F.32总结了tablefunc模块提供的函数。
表 F.32.tablefunc函数
F.43.1.1.正态分布#
normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8正态分布生成一组正态分布的随机值（高斯分布）。
*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.43.1.2.交叉表(文本)#
crosstab(text sql)
crosstab(text sql, int N)交叉表函数用于生成“透视”显示，其中数据横向列在页面上，而不是纵向列出。例如，我们可能有如下数据
row1 val11
row1 val12
row1 val13
...
row2 val21
row2 val22
row2 val23
...我们希望像下面这样显示
row1 val11 val12 val13 ...
row2 val21 val22 val23 ...
...交叉表函数采用一个文本参数，该参数是一个 SQL 查询，生成以第一种方式格式化的原始数据，并生成以第二种方式格式化的表。
*sql参数是一个 SQL 语句，它会生成源数据集。此语句必须返回一个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 val8crosstab函数被声明为返回setof record，因此输出列的实际名称和类型必须在调用SELECT语句的FROM子句中定义，例如
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);此示例生成类似这样的一个集合
<== value columns ==>
row_name category_1 category_2
----------+------------+------------
row1 val1 val2
row2 val5 val6FROM子句必须将输出定义为一个row_name列（与 SQL 查询的第一个结果列具有相同的数据类型），后跟 N 个value列（全部与 SQL 查询的第三个结果列具有相同的数据类型）。您可以根据需要设置任意数量的输出值列。输出列的名称由您决定。
crosstab函数为具有相同row_name值的每个连续输入行组生成一个输出行。它使用这些行的value字段从左到右填充输出value列。如果组中的行数少于输出value列，则额外的输出列将填充为 null；如果行数多，则会跳过额外的输入行。
在实践中，SQL 查询应始终指定ORDER BY 1,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)您可以通过设置一个自定义交叉表函数，将所需的输出行类型连接到其定义中，从而避免总是必须写出FROM子句来定义输出列。这将在下一节中进行描述。另一种可能性是在视图定义中嵌入所需的FROM子句。
注意
另请参阅psql中的[\crosstabview](app-psql.html#APP-PSQL-META-COMMANDS-CROSSTABVIEW)命令，该命令提供类似于crosstab()的功能。
F.43.1.3.crosstab*N*(text)#
crosstabN(text sql)crosstab*N*函数是设置通用crosstab函数的自定义包装器的示例，这样您不必在调用SELECT查询中写出列名和类型。tablefunc模块包括crosstab2、crosstab3和crosstab4，其输出行类型定义为
CREATE TYPE tablefunc_crosstab_N AS (
row_name TEXT,
category_1 TEXT,
category_2 TEXT,
.
.
.
category_N TEXT
);因此，当输入查询生成row_name和value列类型为text，并且您需要 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中的示例。然后定义一个唯一的函数名，接受一个text参数并返回setof your_type_name，但链接到相同的底层crosstabC 函数。例如，如果您的源数据生成的行名称为text，值则为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.43.1.4.crosstab(text, text)#
crosstab(text source_sql, text category_sql)crosstab的单参数形式的主要限制在于它将组中的所有值视为相同，将每个值插入到第一个可用列中。如果您希望值列对应于特定的数据类别，并且某些组可能没有某些类别的相关数据，那么这将无法很好地工作。crosstab的双参数形式通过提供与输出列相对应的类别的显式列表来处理这种情况。
*source_sql*是一条产生数据源集的 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是一条产生类别集的 SQL 语句。此语句必须仅返回一列。它必须至少产生一行，否则将生成错误。此外，它不能产生重复值，否则将生成错误。category_sql*可能类似于
SELECT DISTINCT cat FROM foo ORDER BY 1;
cat
-------
cat1
cat2
cat3
cat4crosstab函数被声明为返回setof record，因此输出列的实际名称和类型必须在调用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 val8FROM子句必须定义适当数据类型的适当数量的输出列。如果*source_sql查询结果中有N列，则前N-2 列必须与前N-2 个输出列匹配。其余输出列必须具有source_sql查询结果的最后一列的类型，并且它们的数目必须与category_sql*查询结果中的行数完全相同。
crosstab函数为具有相同row_name值的每个连续输入行组生成一行输出。输出row_name列以及任何“extra”列都从该组的第一行复制。输出value列用具有匹配category值的行中的value字段填充。如果行的category与*category_sql*查询的任何输出不匹配，则其value将被忽略。在组的任何输入行中都不存在匹配类别的输出列都将用空值填充。
在实践中，*source_sql查询应始终指定ORDER BY 1以确保具有相同row_name的值被组合在一起。但是，组内类别的顺序并不重要。此外，务必确保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的底层 C 函数名为crosstab_hash。
F.43.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可以显示从任何行派生的子树。
表 F.33说明了参数。
表 F.33.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);
-- 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.43.2. 作者#
乔·康威