F.38. tablefunc
tablefunc模块包括返回表(即多行)的各种函数。这些函数本身很有用，也可以用作示例编写返回多行的 C 函数的示例。
F.38.1. 提供的功能
Table F.30显示tablefunc模块提供的功能。
表 F.30.tablefunc功能
F.38.1.1. 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)
F.38.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*参数是一个 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值的每一连续 Importing 行组产生一个输出行。它使用这些行中的value字段从左至右填充输出value列。如果组中的行少于输出value列，则多余的输出列将填充为空；如果有更多行，则跳过多余的 Importing 行。
在实践中，SQL 查询应始终指定ORDER BY 1,2，以确保 Importing 行正确排序，也就是说，具有相同row_name的值将放在一起并在行内正确排序。注意crosstab本身并不关注查询结果的第二列；它只是要排序的位置，以控制第三列值在页面上的显示 Sequences。
这是一个完整的示例：
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子句。
Note
另请参见 psql 中的\crosstabview命令，该命令提供的功能类似于crosstab()。
F.38.1.3. crosstabN(text)
crosstabN(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
);
因此，当 Importing 查询生成类型为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，但链接到相同的基础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.38.1.4. 交叉表(文本，文本)
crosstab(text source_sql, text category_sql)
crosstab的单参数形式的主要局限性在于，它会将一组中的所有值都一样对待，并将每个值插入到第一个可用列中。如果您希望值列对应于特定的数据类别，并且某些组可能没有某些类别的数据，则效果不佳。 crosstab的两参数形式通过提供与输出列相对应的类别的显式列表来处理这种情况。
source_sql*是产生数据源集的 SQL 语句。该语句必须返回一个row_name列，一个category列和一个value列。它还可能有一个或多个“额外”列。row_name列必须在第一位。category和value列必须是该 Sequences 的最后两列。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值的每一连续 Importing 行组产生一个输出行。从该组的第一行复制输出row_name列以及所有“额外”列。在输出value列中填充了具有匹配category值的行中的value字段。如果某行的category与* category_sql *查询的任何输出都不匹配，则将其value忽略。在组的任何 Importing 行中不存在匹配类别的输出列将填充为空。
在实践中，* source_sql 查询应始终指定ORDER BY 1以确保将具有相同row_name的值放在一起。但是，组中类别的排序并不重要。同样，必须确保 category_sql *查询的输出 Sequences 与指定的输出列 Sequences 相匹配。
这是两个完整的示例：
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)
您可以创建 sched 义函数，以避免在每个查询中写出结果列名称和类型。请参阅上一节中的示例。此crosstab形式的基础 C 函数名为crosstab_hash。
F.38.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.31解释参数。
表 F.31.connectby参数
| Parameter | Description |
|---|---|
relname |
源关系的名称 |
keyid_fld |
关键字段名称 |
parent_keyid_fld |
父键字段的名称 |
orderby_fld |
排序同级的字段名称(可选) |
start_with |
该行的键值开始于 |
max_depth |
下降到的最大深度，或者为无限深度为零 |
branch_delim |
用于在分支输出中分隔键的字符串(可选) |
键和父键字段可以是任何数据类型，但是它们必须是相同的类型。请注意，无论键字段的类型如何，都必须将* start_with *值作为文本字符串 Importing。
声明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)
F.38.2. Author
Joe Conway