crosstab 等） #
tablefunc 模块包含多个返回表（即多行）的函数。这些函数本身有用，同时也可作为编写返回多行的 C 函数的示例。
该模块被视为“可信”模块，也就是说，拥有当前数据库 CREATE 权限的非超级用户也可以安装它。
Table F.33 汇总了 tablefunc 模块提供的函数。
Table F.33. tablefunc 函数
normal_rand #normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
normal_rand 产生一组正态分布（高斯分布）的随机值。
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)
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 参数是一条产生源数据集的 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 val8
crosstab 函数声明为返回 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 val6
FROM 子句必须把输出定义为一个 row_name 列（数据类型与 SQL 查询的第一个结果列相同），后跟 N 个 value 列（数据类型都与 SQL 查询的第三个结果列相同）。你可以设置任意多的输出值列。输出列的名称由你决定。
crosstab 函数为每个具有相同 row_name 值的连续输入行组产生一个输出行。它按从左到右的顺序，用这些行的 value 字段填充输出 value 列。如果组内的行数少于输出 value 列的数量，多余的输出列用空值填充；如果行数更多，多余的输入行会被跳过。
在实践中，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)
你可以通过设置一个自定义 crosstab 函数（把期望的输出行类型固定在其定义中）来避免每次都要书写定义输出列的 FROM 子句。这将在下一节描述。另一种可能的做法是把所需的 FROM 子句嵌入视图定义中。
另请参见 psql 中的 \crosstabview
命令，它提供与 crosstab() 类似的功能。
crosstabN(text) #
crosstabN(text sql)
crosstab 函数是如何为通用 Ncrosstab 函数设置自定义包装的示例，这样你就无需在调用 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 与 value 列，并且你想要 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 的唯一函数名，但链接到同一个底层 crosstab C 函数。例如，如果你的源数据产生 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;
crosstab(text, text) #crosstab(text source_sql, text category_sql)
单参数形式 crosstab 的主要局限在于，它平等对待组内的所有值，把每个值插入第一个可用的列。如果你希望值列对应特定的数据类别，而某些组可能没有某些类别的数据，那就行不通了。crosstab 的双参数形式通过提供与输出列对应的类别的显式列表来处理这种情况。
source_sql 是一条产生源数据集的 SQL 语句。该语句必须返回一个 row_name 列、一个 category 列与一个 value 列。它还可以有一个或多个“额外”列。row_name 列必须是第一列。category 与 value 列必须是最后两列，并按此顺序。位于 row_name 与 category 之间的任何列都被视为“额外”列。对于具有相同 row_name 值的所有行，“额外”列预期取相同值。
例如，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
cat4
crosstab 函数声明为返回 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 val8
FROM 子句必须定义数量正确、类型正确的输出列。如果 source_sql 查询的结果有 N 列，前 N-2 列必须与前 N-2 个输出列一一对应。其余输出列的类型必须与 source_sql 查询结果的最后一列相同，并且数量必须恰好等于 category_sql 查询结果中的行数。
crosstab 函数为每个具有相同 row_name 值的连续输入行组产生一个输出行。输出的 row_name 列以及任何“额外”列都从该组的第一行复制。输出 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。
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.34 解释了这些参数。
Table F.34. 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_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)
Joe Conway