PostgreSQL: 文档：17：F.41. tablefunc — 返回表的函数（crosstab 等）

文档 → PostgreSQL 17

受支持版本：当前版本 (18) / 17 / 16 / 15 / 14

开发版本： devel

F.41. tablefunc — 返回表的函数（`crosstab` 等）
Prev	Up	Appendix F. 额外提供的模块与扩展	Home	Next

F.41. tablefunc — 返回表的函数（`crosstab` 等） #

F.41.1. 提供的函数
F.41.2. 作者

tablefunc 模块包含多种返回表（即多行结果）的函数。这些函数本身很有用，也可作为如何编写返回多行的 C 函数的示例。

该模块被视为“受信任的”，也就是说，拥有当前数据库 CREATE 权限的非超级用户也可以安装它。

F.41.1. 提供的函数 #

Table F.31 总结了 tablefunc 模块提供的函数。

Table F.31. tablefunc函数

函数描述
`normal_rand` ( `numvals` `integer`, `mean` `float8`, `stddev` `float8` ) → `setof float8` 生成一组正态分布的随机值。
`crosstab` ( `sql` `text` ) → `setof record` 生成一个“透视表”，其中包含行名以及 `N` 个值列，其中 `N` 由调用查询中指定的行类型决定。
`crosstabN` ( `sql` `text` ) → `setof table_crosstab_N` 生成一个“透视表”，其中包含行名以及 `N` 个值列。`crosstab2`、`crosstab3` 和 `crosstab4` 是预定义的，但也可以按下文所述创建额外的 `crosstabN` 函数。
`crosstab` ( `source_sql` `text`, `category_sql` `text` ) → `setof record` 生成一个“透视表”，其值列由第二个查询指定。
`crosstab` ( `sql` `text`, `N` `integer` ) → `setof record` 这是 `crosstab(text)` 的过时版本。参数 `N` 现已被忽略，因为值列的数量始终由调用查询决定。
`connectby` ( `relname` `text`, `keyid_fld` `text`, `parent_keyid_fld` `text` [, `orderby_fld` `text` ], `start_with` `text`, `max_depth` `integer` [, `branch_delim` `text` ] ) → `setof record` 生成层次树结构的表示形式。

函数

描述

normal_rand ( numvals integer, mean float8, stddev float8 ) → setof float8

生成一组正态分布的随机值。

crosstab ( sql text ) → setof record

生成一个“透视表”，其中包含行名以及 N 个值列，其中 N 由调用查询中指定的行类型决定。

crosstabN ( sql text ) → setof table_crosstab_N

生成一个“透视表”，其中包含行名以及 N 个值列。crosstab2、crosstab3 和 crosstab4 是预定义的，但也可以按下文所述创建额外的 crosstabN 函数。

crosstab ( source_sql text, category_sql text ) → setof record

生成一个“透视表”，其值列由第二个查询指定。

crosstab ( sql text, N integer ) → setof record

这是 crosstab(text) 的过时版本。参数 N 现已被忽略，因为值列的数量始终由调用查询决定。

connectby ( relname text, keyid_fld text, parent_keyid_fld text [, orderby_fld text ], start_with text, max_depth integer [, branch_delim text ] ) → setof record

生成层次树结构的表示形式。

F.41.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.41.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 值的每个连续分组生成一行输出。它会用这些行中的 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 子句。

Note

另见 psql 中的 \crosstabview 命令，它提供了与 crosstab() 类似的功能。

F.41.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
);

因此，当输入查询产生类型为 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;

F.41.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
    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 列以及任何“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.41.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.32 解释了这些参数。

Table F.32. 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 参数和 branch 列即可。

如果同一父节点下各同级节点的顺序很重要，可以包含 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.41.2. 作者 #

Joe Conway

Prev	Up	Next
F.40. sslinfo — 获取客户端 SSL 信息	Home	F.42. tcn — 用于向监听者通知表内容变更的触发器函数

提交更正

如果您发现文档中有不正确的内容、与您使用特定功能的经验不符或需要进一步说明，请使用此表单来报告文档问题。

F.41. tablefunc — 返回表的函数（crosstab 等） #

F.41.1. 提供的函数 #

F.41.1.1. normal_rand #

F.41.1.2. crosstab(text) #