CREATE AGGREGATE

CREATE AGGREGATE — 定义新的聚合函数

概要

CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

CREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
                        ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , INITCOND = initial_condition ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
    [ , HYPOTHETICAL ]
)

or the old syntax

CREATE [ OR REPLACE ] AGGREGATE name (
    BASETYPE = base_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
)

描述

CREATE AGGREGATE定义一个新的聚合函数。CREATE OR REPLACE AGGREGATE将定义一个新的聚合函数或替换现有定义。发行版中包含一些基本且常用的聚合函数；它们在第 9.21 节中进行了说明。如果定义了新类型或需要尚未提供的聚合函数，则可以使用CREATE AGGREGATE提供所需功能。

替换现有定义时，不能更改参数类型、结果类型和直接参数的数量。此外，新定义的类型（普通聚合、有序集聚合或假设集聚合）必须与旧定义相同。

如果给出了模式名称（例如，CREATE AGGREGATE myschema.myagg ...），则聚合函数在指定的模式中创建。否则，它将在当前模式中创建。

聚合函数由其名称和输入数据类型标识。如果聚合函数在不同输入类型上运行，则同一模式中的两个聚合函数可以具有相同的名称。聚合函数的名称和输入数据类型还必须不同于同一模式中每个普通函数的名称和输入数据类型。此行为与普通函数名称的重载相同（请参阅CREATE FUNCTION）。

一个简单的聚合函数由一个或两个普通函数构成：一个状态转换函数*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与第一个arg_data_type*相同时才可用。当这些类型不同时，你必须提供一个非 null 初始条件或使用非严格转换函数。

如果状态转换函数不是严格的，则在每一行输入中都会无条件地调用它，并且必须自己处理 null 输入和 null 状态值。这允许聚合作者完全控制聚合处理 null 值的方式。

如果最终函数声明为“strict”，则在结束状态值为 null 时不会调用它；而是会自动返回一个 null 结果。（当然，这只是严格函数的正常行为。）在任何情况下，最终函数都可以选择返回一个 null 值。例如，avg的最终函数在看到有零个输入行时返回 null。

有时将最终函数声明为不仅接受状态值，还接受与聚合输入值相对应的额外参数很有用。这样做的主要原因是，如果最终函数是多态的，并且状态值的数据类型不足以确定结果类型。这些额外参数总是作为 NULL 传递（因此在使用FINALFUNC_EXTRA选项时，最终函数不能是严格的），但它们仍然是有效的参数。例如，最终函数可以使用get_fn_expr_argtype来识别当前调用中的实际参数类型。

聚合可以选择支持移动聚合模式，如第 38.12.1 节中所述。这需要指定MSFUNC、MINVFUNC和MSTYPE参数，还可以选择指定MSSPACE、MFINALFUNC、MFINALFUNC_EXTRA、MFINALFUNC_MODIFY和MINITCOND参数。除了MINVFUNC，这些参数的工作方式与没有M的相应简单聚合参数相同；它们定义了聚合的单独实现，其中包括逆转换函数。

参数列表中带有ORDER BY的语法创建一种特殊类型的聚合，称为有序集聚合；或者如果指定了HYPOTHETICAL，则创建假设集聚合。这些聚合以依赖于顺序的方式对已排序值的组进行操作，因此指定输入排序顺序是调用中不可或缺的一部分。此外，它们可以有直接参数，这些参数只在每次聚合时求值一次，而不是在每行输入时求值一次。假设集聚合是有序集聚合的一个子类，其中一些直接参数在数量和数据类型上必须与聚合参数列匹配。这允许将这些直接参数的值作为附加的“假设”行添加到聚合输入行集合中。

聚合可以选择支持部分聚合，如第 38.12.4 节中所述。这需要指定COMBINEFUNC参数。如果*state_data_type*是internal，通常还应该提供SERIALFUNC和DESERIALFUNC参数，以便进行并行聚合。请注意，聚合还必须标记为PARALLEL SAFE才能启用并行聚合。

类似于MIN或MAX的行为的聚合有时可以通过查看索引而不是扫描每一行输入来进行优化。如果此聚合可以这样优化，请通过指定排序运算符来指示它。基本要求是聚合必须产生由运算符诱导的排序顺序中的第一个元素；换句话说

SELECT agg(col) FROM tab;

必须等同于

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

进一步的假设是聚合忽略空输入，并且当且仅当没有非空输入时才提供空结果。通常，数据类型的<运算符是MIN的正确排序运算符，>是MAX的正确排序运算符。请注意，除非指定的运算符是 B 树索引运算符类的“小于”或“大于”策略成员，否则优化永远不会实际生效。

要能够创建聚合函数，您必须对参数类型、状态类型和返回类型拥有USAGE权限，以及对支持函数拥有EXECUTE权限。

参数

name

要创建的聚合函数的名称（可选模式限定）。

argmode

参数的模式：IN 或 VARIADIC。（聚合函数不支持 OUT 参数。）如果省略，则默认为 IN。只有最后一个参数可以标记为 VARIADIC。

argname

参数的名称。目前仅对文档目的有用。如果省略，则参数没有名称。

arg_data_type

此聚合函数操作的输入数据类型。要创建零参数聚合函数，请在参数规范列表中编写 *。（此类聚合的示例是 count(*)。）

base_type

在 CREATE AGGREGATE 的旧语法中，输入数据类型由 basetype 参数指定，而不是写在聚合名称旁边。请注意，此语法只允许一个输入参数。要使用此语法定义零参数聚合函数，请将 basetype 指定为 "ANY"（而不是 *）。使用旧语法无法定义有序集聚合。

sfunc

为每一行输入调用的状态转换函数的名称。对于一个普通的 N 参数聚合函数，sfunc 必须采用 N+1 个参数，第一个参数的类型为 state_data_type，其余参数与聚合的已声明输入数据类型相匹配。此函数必须返回 state_data_type 类型的返回值。此函数采用当前状态值和当前输入数据值，并返回下一个状态值。

对于有序集（包括假设集）聚合，状态转换函数仅接收当前状态值和聚合参数，而不是直接参数。否则，它是一样的。

state_data_type

聚合状态值的数据类型。

state_data_size

聚合状态值的近似平均大小（以字节为单位）。如果省略此参数或为零，则基于state_data_type使用默认估计值。规划器使用此值来估计分组聚合查询所需的内存。

ffunc

在遍历所有输入行后调用以计算聚合结果的最终函数的名称。对于普通聚合，此函数必须采用一个state_data_type类型的参数。聚合的返回数据类型定义为此函数的返回类型。如果未指定ffunc，则结束状态值将用作聚合结果，并且返回类型为state_data_type。

对于有序集（包括假设集）聚合，最终函数不仅接收最终状态值，还接收所有直接参数的值。

如果指定FINALFUNC_EXTRA，则除了最终状态值和任何直接参数外，最终函数还接收与聚合的常规（聚合）参数相对应的额外 NULL 值。这主要有助于在定义多态聚合时允许正确解析聚合结果类型。

FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

此选项指定最终函数是否是不修改其参数的纯函数。READ_ONLY表示不修改；其他两个值表示它可能会更改转换状态值。有关更多详细信息，请参见下文的备注。默认值为READ_ONLY，但有序集聚合除外，其默认值为READ_WRITE。

combinefunc

可以指定combinefunc函数以允许聚合函数支持部分聚合。如果提供，combinefunc必须组合两个state_data_type值（每个值都包含对输入值某个子集的聚合结果）以生成新的state_data_type，该值表示对两组输入进行聚合的结果。可以将此函数视为sfunc，其中它不是对单个输入行执行操作并将其添加到正在运行的聚合状态，而是将另一个聚合状态添加到正在运行的状态。

必须将combinefunc声明为采用两个state_data_type参数并返回state_data_type值的函数。此函数还可以选择为“strict”。在这种情况下，当任何输入状态为 null 时，都不会调用函数；另一个状态将被视为正确结果。

对于其 state_data_type 为 internal 的聚合函数，combinefunc 不应严格。在这种情况下，combinefunc 必须确保正确处理空状态，并且返回的状态正确存储在聚合内存上下文中。

serialfunc

如果其 state_data_type 为 internal 的聚合函数具有 serialfunc 函数，则它只能参与并行聚合，该函数必须将聚合状态序列化为 bytea 值，以便传输到另一个进程。此函数必须采用一个类型为 internal 的参数，并返回类型为 bytea 的值。还需要相应的 deserialfunc。

deserialfunc

将先前序列化的聚合状态反序列化回 state_data_type。此函数必须采用两个类型为 bytea 和 internal 的参数，并生成类型为 internal 的结果。（注意：第二个 internal 参数未使用，但出于类型安全原因需要它。）

initial_condition

状态值的初始设置。这必须是数据类型 state_data_type 接受的形式的字符串常量。如果未指定，则状态值将开始为 null。

msfunc

在移动聚合模式下为每个输入行调用的正向状态转换函数的名称。这与常规转换函数完全相同，只是它的第一个参数和结果的类型为 mstate_data_type，这可能不同于 state_data_type。

minvfunc

在移动聚合模式下使用的反向状态转换函数的名称。此函数具有与 msfunc 相同的参数和结果类型，但它用于从当前聚合状态中删除值，而不是向其中添加值。反向转换函数必须具有与正向状态转换函数相同的严格性属性。

mstate_data_type

在使用移动聚合模式时，聚合状态值的数据类型。

mstate_data_size

在使用移动聚合模式时，聚合状态值的大致平均大小（以字节为单位）。这与 state_data_size 的工作方式相同。

mffunc

在使用移动聚合模式时，在遍历所有输入行后调用以计算聚合结果的最终函数的名称。它的工作方式与 ffunc 相同，但其第一个参数的类型为 mstate_data_type，并且通过编写 MFINALFUNC_EXTRA 指定额外的虚拟参数。由 mffunc 或 mstate_data_type 确定的聚合结果类型必须与聚合的常规实现确定的类型匹配。

MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

此选项类似于 FINALFUNC_MODIFY，但它描述了移动聚合最终函数的行为。

minitial_condition

在使用移动聚合模式时的状态值的初始设置。它的工作方式与 initial_condition 相同。

sort_operator

类似于 MIN 或 MAX 的聚合的关联排序运算符。这只是一个运算符名称（可能符合架构限定）。该运算符被假定具有与聚合相同的数据输入类型（该聚合必须是单参数普通聚合）。

PARALLEL = { SAFE | RESTRICTED | UNSAFE }

PARALLEL SAFE、PARALLEL RESTRICTED 和 PARALLEL UNSAFE 的含义与 CREATE FUNCTION 中的含义相同。如果聚合被标记为 PARALLEL UNSAFE（这是默认值！）或 PARALLEL RESTRICTED，则不会考虑对其进行并行化。请注意，规划器不会查阅聚合支持函数的并行安全性标记，只查阅聚合本身的标记。

HYPOTHETICAL

仅适用于有序集聚合，此标志指定聚合参数应根据假设集聚合的要求进行处理：即，最后几个直接参数必须与聚合的（WITHIN GROUP）参数的数据类型匹配。HYPOTHETICAL 标志对运行时行为没有影响，只对聚合参数的数据类型和排序规则的解析时行为有影响。

CREATE AGGREGATE的参数可以按任何顺序编写，而不仅仅是上面说明的顺序。

注释

在指定支持函数名称的参数中，如果需要，可以编写模式名称，例如SFUNC = public.sum。但是，不要在那里编写参数类型，因为支持函数的参数类型是由其他参数决定的。

通常，PostgreSQL 函数应为不修改其输入值的真函数。但是，聚合转换函数在聚合上下文中使用时允许作弊并修改其转换状态参数。与每次制作转换状态的新副本相比，这可以提供巨大的性能优势。

同样，虽然通常期望聚合最终函数不修改其输入值，但有时避免修改转换状态参数是不切实际的。必须使用FINALFUNC_MODIFY参数声明此类行为。READ_WRITE值表示最终函数以未指定的方式修改转换状态。此值阻止将聚合用作窗口函数，并且还阻止合并具有相同输入值和转换函数的聚合调用的转换状态。SHAREABLE值表示转换函数不能在最终函数之后应用，但可以对结束转换状态值执行多个最终函数调用。此值阻止将聚合用作窗口函数，但允许合并转换状态。（也就是说，这里感兴趣的优化不是重复应用相同的最终函数，而是将不同的最终函数应用于相同的结束转换状态值。只要没有最终函数被标记为READ_WRITE，就可以这样做。）

如果聚合支持移动聚合模式，当聚合用作具有移动帧起始位置的窗口的窗口函数时，它将提高计算效率（即，帧起始模式不为UNBOUNDED PRECEDING）。从概念上讲，正向转换函数在输入值从底部进入窗口帧时将它们添加到聚合状态，反向转换函数在它们从顶部离开帧时再次删除它们。因此，当删除值时，它们总是按添加顺序删除。因此，每当调用反向转换函数时，它将接收最早添加但尚未删除的参数值。反向转换函数可以假设在删除最旧行后，当前状态中至少会保留一行。（如果情况并非如此，窗口函数机制只会启动新的聚合，而不是使用反向转换函数。）

移动聚合模式的正向转换函数不允许将 NULL 返回为新状态值。如果反向转换函数返回 NULL，则将其视为反向函数无法对此特定输入逆转状态计算的指示，因此将从当前帧起始位置重新完成聚合计算。此约定允许在某些情况下使用移动聚合模式，在这种情况下，有一些不经常出现的情况不适合从运行状态值中逆转出来。

如果没有提供移动聚合实现，聚合仍然可以与移动帧一起使用，但是PostgreSQL将在帧的开始移动时重新计算整个聚合。请注意，无论聚合是否支持移动聚合模式，PostgreSQL都可以在不重新计算的情况下处理移动帧结束；这是通过继续向聚合状态添加新值来完成的。这就是将聚合用作窗口函数需要最终函数为只读的原因：它不能破坏聚合的状态值，以便即使已为一组帧边界获取了聚合结果值，也可以继续聚合。

有序集聚合的语法允许为最后一个直接参数和最后一个聚合（WITHIN GROUP）参数指定VARIADIC。但是，当前的实现以两种方式限制了VARIADIC的使用。首先，有序集聚合只能使用VARIADIC "any"，而不是其他变长数组类型。其次，如果最后一个直接参数是VARIADIC "any"，那么只能有一个聚合参数，并且它也必须是VARIADIC "any"。（在系统目录中使用的表示中，这两个参数合并为一个VARIADIC "any"项，因为pg_proc无法表示具有多个VARIADIC参数的函数。）如果聚合是假设集聚合，则与VARIADIC "any"参数匹配的直接参数是假设参数；任何前面的参数都表示不约束为与聚合参数匹配的其他直接参数。

目前，有序集聚合不需要支持移动聚合模式，因为它们不能用作窗口函数。

目前不支持有序集聚合的部分（包括并行）聚合。此外，它永远不会用于包含DISTINCT或ORDER BY子句的聚合调用，因为在部分聚合期间无法支持这些语义。

示例

请参见第 38.12 节。

兼容性

CREATE AGGREGATE是PostgreSQL语言扩展。SQL 标准未提供用户定义的聚合函数。

另请参阅

ALTER AGGREGATE，DROP AGGREGATE

CREATE AGGREGATE ​

概要 ​

描述 ​

参数 ​

注释 ​

示例 ​

兼容性 ​

另请参阅 ​

CREATE AGGREGATE

概要

描述

参数

注释

示例

兼容性

另请参阅