F.38. tablefunc

tablefunc模块包括返回表(即多行)的各种函数。这些函数本身很有用,也可以用作示例编写返回多行的 C 函数的示例。

F.38.1. 提供的功能

Table F.30显示tablefunc模块提供的功能。

表 F.30.tablefunc功能

FunctionReturnsDescription
normal_rand(int numvals, float8 mean, float8 stddev)setof float8产生一组正态分布的随机值
crosstab(text sql)setof record产生一个“数据透视表”,其中包含行名加* N 值列,其中 N *由调用查询中指定的行类型确定
crosstabN(text sql)setof table_crosstab_N生成一个“数据透视表”,其中包含行名以及* N *值列。 crosstab2crosstab3crosstab4是 sched 义的,但是您可以按如下所述创建其他crosstabN函数
crosstab(text source_sql, text category_sql)setof record产生具有第二个查询指定的值列的“数据透视表”
crosstab(text sql, int N)setof recordcrosstab(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.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模块包括crosstab2crosstab3crosstab4,其输出行类型定义为

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);

因此,当 Importing 查询生成类型为textrow_namevalue列并且您需要 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.38.1.4. 交叉表(文本,文本)

crosstab(text source_sql, text category_sql)

crosstab的单参数形式的主要局限性在于,它会将一组中的所有值都一样对待,并将每个值插入到第一个可用列中。如果您希望值列对应于特定的数据类别,并且某些组可能没有某些类别的数据,则效果不佳。 crosstab的两参数形式通过提供与输出列相对应的类别的显式列表来处理这种情况。

  • source_sql *是产生数据源集的 SQL 语句。该语句必须返回一个row_name列,一个category列和一个value列。它还可能有一个或多个“额外”列。 row_name列必须在第一位。 categoryvalue列必须是该 Sequences 的最后两列。 row_namecategory之间的任何列均被视为“额外”。带有“ 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参数

ParameterDescription
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