Postgresql 中文操作指南

CREATE AGGREGATE

CREATE AGGREGATE — 定义新的聚合函数

Synopsis

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 ]
)

Description

CREATE AGGREGATE 定义新的聚合函数。 CREATE OR REPLACE AGGREGATE 将定义新的聚合函数或替换现有定义。发行版中包含了一些基本且常用的聚合函数；它们在 Section 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 开始。

如果状态转换函数声明为“严格的”，则不能调用它带有 null 输入。使用这样的转换函数，聚合执行的行为如下。忽略任何 null 输入值的行的（不调用函数，并且保留前一个状态值）。如果初始状态值为 null，则在第一行具有全非 null 输入值时，第一个参数值将替换状态值，并且在具有全非 null 输入值的每个后续行中调用转换函数。这对于实现 max 之类的聚合非常方便。请注意，仅当 state_data_type 与第一个 arg_data_type 相同，才可用此行为。当这些类型不同时，你必须提供一个非空初始条件或使用非严格转换函数。

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

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

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

聚合可以选择支持 moving-aggregate mode ，如 Section 38.12.1 中所述。这需要指定 MSFUNC 、 MINVFUNC 和 MSTYPE 参数，并可选地指定 MSSPACE 、 MFINALFUNC 、 MFINALFUNC_EXTRA 、 MFINALFUNC_MODIFY 和 MINITCOND 参数。除了 MINVFUNC 外，这些参数的工作原理与没有 M 的相应简单聚合参数类似；它们定义聚合函数的单独实现，其中包括逆转换函数。

参数列表中使用 ORDER BY 的语法将创建一个特殊类型的聚合，称为 ordered-set aggregate ；或者如果指定了 HYPOTHETICAL ，那么将创建 hypothetical-set aggregate 。这些聚合函数对已排序值组以依赖顺序的方式进行操作，因此在调用中指定输入排序顺序是必要的。此外，它们可以带有 direct 参数，这些参数在每个聚合过程中仅计算一次，而不是在每一行输入时计算一次。假定集合聚合是有序集合聚合的子类，其中某些直接参数需要与聚合的参数列在数量和数据类型上匹配。这允许将这些直接参数的值作为附加的“假定”行添加到聚合输入行的集合中。

聚合可以选择支持 partial aggregation ，如 Section 38.12.4 中所述。这需要指定 COMBINEFUNC 参数。如果 state_data_type 为 internal ，通常还应提供 SERIALFUNC 和 DESERIALFUNC 参数，以使并行聚合成为可能。请注意，还必须将聚合标记为 PARALLEL SAFE 以启用并行聚合。

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

SELECT agg(col) FROM tab;

必须等同于：

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

此外，聚合还忽略空输入，只在没有非空输入时才返回空结果。一般情况下，数据类型的 < 运算符是 MIN 的正确排序运算符，而 > 是 MAX 的正确排序运算符。请注意，只有在指定的运算符是 B 树索引运算符类的“小于”或“大于”策略成员时，此优化才实际生效。

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

Parameters

  • 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

    • 聚合状态值的大致平均大小（以字节为单位）。如果省略或为 0，则基于 state_data_type 根据默认估算使用。计划程序使用该值估算分组聚合查询所需的内存。

  • ffunc

    • 遍历完所有输入行后计算聚合理结果所调用的最终函数的名称。对于一般聚合，此函数必须获取一个类型为 state_data_type 的参数。聚合的返回数据类型定义为此函数的返回类型。如果未指定 ffunc ，则使用最终状态值作为聚合理结果，并且返回类型为 state_data_type 。

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

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

  • FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

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

  • combinefunc

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

    • combinefunc 必须声明为获取两个 state_data_type 参数并返回 state_data_type 值。此函数可以选择为“严格”。在这种情况下，如果任一输入状态为 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 的参数可以被以任何顺序来写入，而不仅仅是上面描述的顺序。

Notes

在指定支持函数名称的参数中，您可以在需要时写入一个模式名称，例如， SFUNC = public.sum 。不过，不要在那里写入参数类型——支持函数的参数类型由其他参数确定。

通常，PostgreSQL 函数应该被期望为不修改它们输入值的真函数。不过，聚合转换函数 when used in the context of an aggregate 被允许作弊并就地修改其转换状态参数。和每次都制作新的转换状态副本相比，这可以提供可观的性能收益。

同样的，当通常希望聚合最终函数不修改其输入值时，有时避免修改转换状态参数是不切实际的。必须使用 FINALFUNC_MODIFY 参数来声明这种行为。 READ_WRITE 值表明最终函数修改转换状态的方式未说明。这个值会阻止该聚合被用作窗口函数，而且它也会阻止对转换状态进行合并，因为聚合调用共享了相同的输入值和转换函数。 SHAREABLE 值表明转换函数不能在最终函数之后应用，但可以在结束转换状态值上执行多个最终函数调用。这个值会阻止该聚合被用作窗口函数，但它允许转换状态合并。（也就是说，此处感兴趣的优化不是反复应用相同的最终函数，而是将不同的最终函数应用于相同的结束转换状态值。只要没有最终函数被标记为 READ_WRITE ，这样做的做法是允许的。）

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

移动聚合模式的正向转换函数不允许返回 NULL 作为新状态值。如果逆转换函数返回 NULL，则将其视为指示逆函数无法对此特定输入逆转状态计算，因此聚合计算将针对当前帧起始位置从头开始重新完成。此约定允许在有不常见情况 impractical 无法从中移除运行状态值的情况下使用移动聚合模式。

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

有序集聚合的语法允许为最后的直接参数和最后一个聚合（ WITHIN GROUP ）参数指定 VARIADIC 。但是，当前实现对 VARIADIC 的使用进行了两种限制。首先，有序集聚合只能使用 VARIADIC "any" ，不能使用其他变长数组类型。其次，如果最后的直接参数时 VARIADIC "any" ，则聚合参数只能有一个并也必须是 VARIADIC "any" 。（在系统目录中使用的表示形式中，这两个参数被合并为一个 VARIADIC "any" 项，因为 pg_proc 不能表示具有多个 VARIADIC 参数的函数。）如果聚合是一个虚拟集聚合，则与 VARIADIC "any" 参数匹配的直接参数就是虚拟参数；任何前面的参数表示未被限制与聚合参数匹配的其他直接参数。

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

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

Examples

请参见 Section 38.12 。

Compatibility

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

See Also

ALTER AGGREGATE ， DROP AGGREGATE