postgres 聚集函数编写 教程

CREATE AGGREGATE

名称

CREATE AGGREGATE -- 定义一个新的聚集函数

语法

CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    [ , SORTOP = sort_operator ]
)

下面是旧语法

CREATE AGGREGATE name (
    BASETYPE = base_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    [ , SORTOP = sort_operator ]
)

描述

CREATE AGGREGATE 定义一个新的聚集函数。一些常用的聚集函数已经包含在基础软件包里了；在节9.15里有文档记录。如果你需要定义一个新类型或需要一个还没有提供的聚集函数，这时 CREATE AGGREGATE 便可排上用场。

如果给出了一个模式的名字(比如 CREATE AGGREGATE myschema.myagg ...)，那么该聚集函数是在指定模式中创建的。否则它是在当前模式中创建的。

一个聚集函数是用它的名字和输入数据类型来标识的。同一模式中如果两个聚集处理的输入数据不同，它们可以有相同的名字。一个聚集函数的输入数据类型必须和所有同一模式中的普通函数的名字和输入类型不同。

一个聚集函数是用一个或两个普通函数做成的：一个状态转换函数 sfunc 和一个可选的最终计算函数 ffunc 。它们是这样使用的：

sfunc( internal-state, next-data-values ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value

PostgreSQL 创建一个类型为 stype 的临时变量。它保存这个聚集的当前内部状态。对于每个输入数据条目，都调用状态转换函数计算内部状态值的新数值。在处理完所有数据后，调用一次最终处理函数以计算聚集的返回值。如果没有最终处理函数，则将最后的状态值当做返回值。

一个聚集函数还可能提供一个初始条件，也就是内部状态值的初始值。这个值是作为一个类型为 text 的字段存储在数据库里的，不过它们必须是状态值数据类型的合法的外部表现形式的常量。如果没有提供状态，那么状态值初始化为 NULL 。

如果该状态转换函数被定义为"strict"，那么就不能用 NULL 输入调用它。此时，聚集的执行如下所述。带有任何 NULL 输入值的行将被忽略(不调用此函数并且保留前一个状态值)。如果初始状态值是 NULL ，那么在第一个含有非 NULL 值的行上，使用第一个参数值替换状态值，然后状态转换函数在随后所有的含有非 NULL 值的行上调用。这样做让比较容易实现像 max 这样的聚集。请注意这种行为只是当 state_data_type 与 input_data_type 相同的时候才表现出来。如果这些类型不同，你必须提供一个非 NULL 的初始条件或者使用一个非"strice"的状态转换函数。

如果状态转换函数不是严格(strict)的，那么它将无条件地在每个输入行上调用，并且必须自行处理 NULL 输入和 NULL 转换值，这样就允许聚集的作者对聚集中的 NULL 有完全的控制。

如果最终转换函数定义为"strict"，那么如果最终状态值是 NULL 时就不会调用它；而是自动输出一个 NULL 结果。这才是 strict 函数的正常特征。不管是那种情况，最终处理函数可以自由选择是否返回 NULL 。比如，avg 的最终处理函数在零输入记录时就会返回 NULL 。

行为类似 MIN 或 MAX 的聚集有时候可以优化为使用索引，而不用扫描每个输入行。如果这个聚集可以如此优化，则用一个排序操作符标识它。这里基本的要求是聚集必须以操作符归纳出来的排序顺序生成第一个元素；换句话说

SELECT agg(col) FROM tab;

必须等于

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;

更多的假设是聚集忽略 NULL 输入，并且只有在输入没有非空数值的时候，它才生成 NULL 结果。通常，数据类型的 < 操作符是 MIN 的适用排序操作符，而 > 是 MAX的适用操作符。请注意，除非声明的操作符是 B-tree 索引操作符类的"小于"或者"大于"策略号，否则这种优化将不会生效。

参数

name

要创建的聚集函数名(可以有模式修饰)

input_data_type

该聚集函数要处理的输入数据类型。要创建一个零参数聚集函数，可以使用 * 代替输入数据类型列表。count(*) 就是这种聚集函数的一个实例。

base_type

在旧式的 CREATE AGGREGATE 语法中，输入数据类型是通过 basetype 参数指定的，而不是写在聚集的名称之后。需要注意的是这种旧式语法仅允许一个输入参数。要创建一个零参数聚集函数，可以将 basetype 指定为 "ANY"(而不是 *)。

sfunc

将在每一个输入行上调用的状态转换函数的名称。对于有 N 个参数的聚集函数，sfunc 必须有 N+1 个参数，其中的第一个参数类型为 state_data_type ，其余的匹配已声明的输入数据类型。函数必须返回一个 state_data_type 类型的值。这个函数接受当前状态值和当前输入数据，并返回下个状态值。

state_data_type

聚集的状态值的数据类型

ffunc

在转换完所有输入行后调用的最终处理函数，它计算聚集的结果。此函数必须接受一个类型为 state_data_type 的参数。聚集的输出数据类型被定义为此函数的返回类型。如果没有声明 ffunc 则使用聚集结果的状态值作为聚集的结果，且输出类型为state_data_type 。

initial_condition

状态值的初始设置(值)。它必须是一个 state_data_type 类型可以接受的文本常量值。如果没有声明，状态值初始为 NULL 。

sort_operator

用于 MIN 或 MAX 类型聚集的排序操作符。这个只是一个操作符名(可以有模式修饰)。这个操作符假设接受和聚集一样的输入数据类型。

CREATE AGGREGATE 的参数可以以任何顺序书写，而不只是上面显示的顺序。

例子

CREATE OR REPLACE FUNCTION _concat(text, text) RETURNS text AS $$ SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2 ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2 END $$ IMMUTABLE LANGUAGE SQL; CREATE AGGREGATE concat ( BASETYPE = text, SFUNC = _concat, STYPE = text );

例子-带简单去重复功能的脚本，并不是很完善，只有局部区域才能使用

CREATE OR REPLACE FUNCTION _concat_distinct(text, text) RETURNS text AS $$ SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2 when $1 like '%'||$2||'%' then $1 ELSE $1 || ',' || $2 END $$ IMMUTABLE LANGUAGE SQL; CREATE AGGREGATE concat_distinct ( BASETYPE = text, SFUNC = _concat_distinct, STYPE = text );