{"Entry":{"collection":"sql","key":"create-aggregate","name":"CREATE AGGREGATE","aliases":["createaggregate"],"metadata":{"aliases":["createaggregate"],"changed_in":["7.0","7.1","7.4","8.1","8.2","9.0","9.4","9.6","11","12","19"],"changes":[{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE AGGREGATE name ( BASETYPE = input_data_type","[ , SFUNC1 = sfunc1, STYPE1 = state1_type ]","[ , SFUNC2 = sfunc2, STYPE2 = state2_type ]"],"removed":["CREATE AGGREGATE name [ AS ]","( BASETYPE    = data_type",", STYPE1    = sfunc1_return_type ]",", STYPE2    = sfunc2_return_type ]"]},"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-createaggregate.htm","to_file":"sql-createaggregate.html"},"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["SFUNC = sfunc, STYPE = state_type","[ , INITCOND = initial_condition ] )"],"removed":["[ , SFUNC1 = sfunc1, STYPE1 = state1_type ]","[ , SFUNC2 = sfunc2, STYPE2 = state2_type ]","[ , INITCOND1 = initial_condition1 ]","[ , INITCOND2 = initial_condition2 ] )"]},"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","examples","see_also"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["STYPE = state_data_type"],"removed":["SFUNC = sfunc, STYPE = state_type"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","examples","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ , SORTOP = sort_operator ]"],"removed":[]},"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE AGGREGATE name ( input_data_type [ , ... ] ) (","SFUNC = sfunc,","STYPE = state_data_type","[ , FINALFUNC = ffunc ]","[ , INITCOND = initial_condition ]","[ , SORTOP = sort_operator ]",")","or the old syntax","BASETYPE = base_type,"],"removed":["BASETYPE = input_data_type,"]},"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["examples"],"removed":[]},"status":"changed","synopsis":{"added":[],"removed":["or the old syntax"]},"to":"9.0"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.2"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (","[ , SSPACE = state_data_size ]","[ , FINALFUNC = ffunc ]","[ , FINALFUNC_EXTRA ]","[ , MSFUNC = msfunc ]","[ , MINVFUNC = minvfunc ]","[ , MSTYPE = mstate_data_type ]","[ , MSSPACE = mstate_data_size ]","[ , MFINALFUNC = mffunc ]","[ , MFINALFUNC_EXTRA ]","[ , MINITCOND = minitial_condition ]","[ , SORTOP = sort_operator ]",")","CREATE 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 ]","[ , INITCOND = initial_condition ]","[ , HYPOTHETICAL ]","[ , SSPACE = state_data_size ]","[ , FINALFUNC = ffunc ]","[ , FINALFUNC_EXTRA ]","[ , MSFUNC = msfunc ]","[ , MINVFUNC = minvfunc ]","[ , MSTYPE = mstate_data_type ]","[ , MSSPACE = mstate_data_size ]","[ , MFINALFUNC = mffunc ]","[ , MFINALFUNC_EXTRA ]","[ , MINITCOND = minitial_condition ]","[ , SORTOP = sort_operator ]"],"removed":["CREATE AGGREGATE name ( input_data_type [ , ... ] ) ("]},"to":"9.4"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ , FINALFUNC_EXTRA ]","[ , COMBINEFUNC = combinefunc ]","[ , SERIALFUNC = serialfunc ]","[ , DESERIALFUNC = deserialfunc ]","[ , SORTOP = sort_operator ]","[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]","[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]","[ , HYPOTHETICAL ]","[ , FINALFUNC_EXTRA ]","[ , COMBINEFUNC = combinefunc ]","[ , SERIALFUNC = serialfunc ]","[ , DESERIALFUNC = deserialfunc ]"],"removed":[]},"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ , FINALFUNC_EXTRA ]","[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]","[ , MFINALFUNC_EXTRA ]","[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]","[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]","[ , INITCOND = initial_condition ]","[ , FINALFUNC_EXTRA ]","[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]","[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]","[ , MINITCOND = minitial_condition ]"],"removed":[]},"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","examples"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (","CREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]","CREATE [ OR REPLACE ] AGGREGATE name ("],"removed":[]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ , SUPPORT = support_function ]","[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]","[ , INITCOND = initial_condition ]","[ , SUPPORT = support_function ]","[ , SUPPORT = support_function ]"],"removed":[]},"to":"19"}],"content_hash":"ac41b8867f13cafa5b2b99aa42a90deb67098e53caf69c2ab584335e3d3f6ddc","editorial":{},"first_version":"6.4","group":"routine","imported_at":"2026-09-30T17:43:36.986049+08:00","last_version":"20","name":"CREATE AGGREGATE","object":"AGGREGATE","position":2002,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new aggregate function","purpose_zh":"","related":["alter-aggregate","drop-aggregate"],"slug":"create-aggregate","source_rev":"a709ab85","synopsis":"CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n[ , SUPPORT = support_function ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n)\n\nCREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]\nORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , INITCOND = initial_condition ]\n[ , SUPPORT = support_function ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n[ , HYPOTHETICAL ]\n)\n\nor the old syntax\n\nCREATE [ OR REPLACE ] AGGREGATE name (\nBASETYPE = base_type,\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n[ , SUPPORT = support_function ]\n)","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-aggregate","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-aggregate","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATEAGGREGATE","file":"sql-createaggregate.html","lang":"en","name":"CREATE AGGREGATE","purpose":"define a new aggregate function","purpose_zh":"","related":["alter-aggregate","drop-aggregate"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e defines a new aggregate function. \u003ccode class=\"command\"\u003eCREATE OR REPLACE AGGREGATE\u003c/code\u003e will either define a new aggregate function or replace an existing definition. Some basic and commonly-used aggregate functions are included with the distribution; they are documented in \u003ca href=\"/docs/18/functions-aggregate.html\" title=\"9.21. Aggregate Functions\"\u003eSection 9.21\u003c/a\u003e. If one defines new types or needs an aggregate function not already provided, then \u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e can be used to provide the desired features.\u003c/p\u003e\u003cp\u003eWhen replacing an existing definition, the argument types, result type, and number of direct arguments may not be changed. Also, the new definition must be of the same kind (ordinary aggregate, ordered-set aggregate, or hypothetical-set aggregate) as the old one.\u003c/p\u003e\u003cp\u003eIf a schema name is given (for example, \u003ccode class=\"literal\"\u003eCREATE AGGREGATE myschema.myagg ...\u003c/code\u003e) then the aggregate function is created in the specified schema. Otherwise it is created in the current schema.\u003c/p\u003e\u003cp\u003eAn aggregate function is identified by its name and input data type(s). Two aggregates in the same schema can have the same name if they operate on different input types. The name and input data type(s) of an aggregate must also be distinct from the name and input data type(s) of every ordinary function in the same schema. This behavior is identical to overloading of ordinary function names (see \u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e).\u003c/p\u003e\u003cp\u003eA simple aggregate function is made from one or two ordinary functions: a state transition function \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e, and an optional final calculation function \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e. These are used as follows:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e( internal-state, next-data-values ) ---\u0026gt; next-internal-state\n\u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e( internal-state ) ---\u0026gt; aggregate-value\n\u003c/pre\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e creates a temporary variable of data type \u003cem class=\"replaceable\"\u003e\u003ccode\u003estype\u003c/code\u003e\u003c/em\u003e to hold the current internal state of the aggregate. At each input row, the aggregate argument value(s) are calculated and the state transition function is invoked with the current state value and the new argument value(s) to calculate a new internal state value. After all the rows have been processed, the final function is invoked once to calculate the aggregate's return value. If there is no final function then the ending state value is returned as-is.\u003c/p\u003e\u003cp\u003eAn aggregate function can provide an initial condition, that is, an initial value for the internal state value. This is specified and stored in the database as a value of type \u003ccode class=\"type\"\u003etext\u003c/code\u003e, but it must be a valid external representation of a constant of the state value data type. If it is not supplied then the state value starts out null.\u003c/p\u003e\u003cp\u003eIf the state transition function is declared \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estrict\u003c/span\u003e”\u003c/span\u003e, then it cannot be called with null inputs. With such a transition function, aggregate execution behaves as follows. Rows with any null input values are ignored (the function is not called and the previous state value is retained). If the initial state value is null, then at the first row with all-nonnull input values, the first argument value replaces the state value, and the transition function is invoked at each subsequent row with all-nonnull input values. This is handy for implementing aggregates like \u003ccode class=\"function\"\u003emax\u003c/code\u003e. Note that this behavior is only available when \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e is the same as the first \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e. When these types are different, you must supply a nonnull initial condition or use a nonstrict transition function.\u003c/p\u003e\u003cp\u003eIf the state transition function is not strict, then it will be called unconditionally at each input row, and must deal with null inputs and null state values for itself. This allows the aggregate author to have full control over the aggregate's handling of null values.\u003c/p\u003e\u003cp\u003eIf the final function is declared \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estrict\u003c/span\u003e”\u003c/span\u003e, then it will not be called when the ending state value is null; instead a null result will be returned automatically. (Of course this is just the normal behavior of strict functions.) In any case the final function has the option of returning a null value. For example, the final function for \u003ccode class=\"function\"\u003eavg\u003c/code\u003e returns null when it sees there were zero input rows.\u003c/p\u003e\u003cp\u003eSometimes it is useful to declare the final function as taking not just the state value, but extra parameters corresponding to the aggregate's input values. The main reason for doing this is if the final function is polymorphic and the state value's data type would be inadequate to pin down the result type. These extra parameters are always passed as NULL (and so the final function must not be strict when the \u003ccode class=\"literal\"\u003eFINALFUNC_EXTRA\u003c/code\u003e option is used), but nonetheless they are valid parameters. The final function could for example make use of \u003ccode class=\"function\"\u003eget_fn_expr_argtype\u003c/code\u003e to identify the actual argument type in the current call.\u003c/p\u003e\u003cp\u003eAn aggregate can optionally support \u003cem class=\"firstterm\"\u003emoving-aggregate mode\u003c/em\u003e, as described in \u003ca href=\"/docs/18/xaggr.html#XAGGR-MOVING-AGGREGATES\" title=\"36.12.1. Moving-Aggregate Mode\"\u003eSection 36.12.1\u003c/a\u003e. This requires specifying the \u003ccode class=\"literal\"\u003eMSFUNC\u003c/code\u003e, \u003ccode class=\"literal\"\u003eMINVFUNC\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eMSTYPE\u003c/code\u003e parameters, and optionally the \u003ccode class=\"literal\"\u003eMSSPACE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eMFINALFUNC\u003c/code\u003e, \u003ccode class=\"literal\"\u003eMFINALFUNC_EXTRA\u003c/code\u003e, \u003ccode class=\"literal\"\u003eMFINALFUNC_MODIFY\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eMINITCOND\u003c/code\u003e parameters. Except for \u003ccode class=\"literal\"\u003eMINVFUNC\u003c/code\u003e, these parameters work like the corresponding simple-aggregate parameters without \u003ccode class=\"literal\"\u003eM\u003c/code\u003e; they define a separate implementation of the aggregate that includes an inverse transition function.\u003c/p\u003e\u003cp\u003eThe syntax with \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e in the parameter list creates a special type of aggregate called an \u003cem class=\"firstterm\"\u003eordered-set aggregate\u003c/em\u003e; or if \u003ccode class=\"literal\"\u003eHYPOTHETICAL\u003c/code\u003e is specified, then a \u003cem class=\"firstterm\"\u003ehypothetical-set aggregate\u003c/em\u003e is created. These aggregates operate over groups of sorted values in order-dependent ways, so that specification of an input sort order is an essential part of a call. Also, they can have \u003cem class=\"firstterm\"\u003edirect\u003c/em\u003e arguments, which are arguments that are evaluated only once per aggregation rather than once per input row. Hypothetical-set aggregates are a subclass of ordered-set aggregates in which some of the direct arguments are required to match, in number and data types, the aggregated argument columns. This allows the values of those direct arguments to be added to the collection of aggregate-input rows as an additional \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003ehypothetical\u003c/span\u003e”\u003c/span\u003e row.\u003c/p\u003e\u003cp\u003eAn aggregate can optionally support \u003cem class=\"firstterm\"\u003epartial aggregation\u003c/em\u003e, as described in \u003ca href=\"/docs/18/xaggr.html#XAGGR-PARTIAL-AGGREGATES\" title=\"36.12.4. Partial Aggregation\"\u003eSection 36.12.4\u003c/a\u003e. This requires specifying the \u003ccode class=\"literal\"\u003eCOMBINEFUNC\u003c/code\u003e parameter. If the \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e is \u003ccode class=\"type\"\u003einternal\u003c/code\u003e, it's usually also appropriate to provide the \u003ccode class=\"literal\"\u003eSERIALFUNC\u003c/code\u003e and \u003ccode class=\"literal\"\u003eDESERIALFUNC\u003c/code\u003e parameters so that parallel aggregation is possible. Note that the aggregate must also be marked \u003ccode class=\"literal\"\u003ePARALLEL SAFE\u003c/code\u003e to enable parallel aggregation.\u003c/p\u003e\u003cp\u003eAggregates that behave like \u003ccode class=\"function\"\u003eMIN\u003c/code\u003e or \u003ccode class=\"function\"\u003eMAX\u003c/code\u003e can sometimes be optimized by looking into an index instead of scanning every input row. If this aggregate can be so optimized, indicate it by specifying a \u003cem class=\"firstterm\"\u003esort operator\u003c/em\u003e. The basic requirement is that the aggregate must yield the first element in the sort ordering induced by the operator; in other words:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT agg(col) FROM tab;\n\u003c/pre\u003e\u003cp\u003emust be equivalent to:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT col FROM tab ORDER BY col USING sortop LIMIT 1;\n\u003c/pre\u003e\u003cp\u003eFurther assumptions are that the aggregate ignores null inputs, and that it delivers a null result if and only if there were no non-null inputs. Ordinarily, a data type's \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e operator is the proper sort operator for \u003ccode class=\"function\"\u003eMIN\u003c/code\u003e, and \u003ccode class=\"literal\"\u003e\u0026gt;\u003c/code\u003e is the proper sort operator for \u003ccode class=\"function\"\u003eMAX\u003c/code\u003e. Note that the optimization will never actually take effect unless the specified operator is the \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eless than\u003c/span\u003e”\u003c/span\u003e or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003egreater than\u003c/span\u003e”\u003c/span\u003e strategy member of a B-tree index operator class.\u003c/p\u003e\u003cp\u003eTo be able to create an aggregate function, you must have \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e privilege on the argument types, the state type(s), and the return type, as well as \u003ccode class=\"literal\"\u003eEXECUTE\u003c/code\u003e privilege on the supporting functions.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the aggregate function to create.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe mode of an argument: \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e or \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e. (Aggregate functions do not support \u003ccode class=\"literal\"\u003eOUT\u003c/code\u003e arguments.) If omitted, the default is \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e. Only the last argument can be marked \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of an argument. This is currently only useful for documentation purposes. If omitted, the argument has no name.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn input data type on which this aggregate function operates. To create a zero-argument aggregate function, write \u003ccode class=\"literal\"\u003e*\u003c/code\u003e in place of the list of argument specifications. (An example of such an aggregate is \u003ccode class=\"function\"\u003ecount(*)\u003c/code\u003e.)\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ebase_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIn the old syntax for \u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e, the input data type is specified by a \u003ccode class=\"literal\"\u003ebasetype\u003c/code\u003e parameter rather than being written next to the aggregate name. Note that this syntax allows only one input parameter. To define a zero-argument aggregate function with this syntax, specify the \u003ccode class=\"literal\"\u003ebasetype\u003c/code\u003e as \u003ccode class=\"literal\"\u003e\"ANY\"\u003c/code\u003e (not \u003ccode class=\"literal\"\u003e*\u003c/code\u003e). Ordered-set aggregates cannot be defined with the old syntax.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the state transition function to be called for each input row. For a normal \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-argument aggregate function, the \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e must take \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e+1 arguments, the first being of type \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e and the rest matching the declared input data type(s) of the aggregate. The function must return a value of type \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e. This function takes the current state value and the current input data value(s), and returns the next state value.\u003c/p\u003e\u003cp\u003eFor ordered-set (including hypothetical-set) aggregates, the state transition function receives only the current state value and the aggregated arguments, not the direct arguments. Otherwise it is the same.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe data type for the aggregate's state value.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe approximate average size (in bytes) of the aggregate's state value. If this parameter is omitted or is zero, a default estimate is used based on the \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e. The planner uses this value to estimate the memory required for a grouped aggregate query.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the final function called to compute the aggregate's result after all input rows have been traversed. For a normal aggregate, this function must take a single argument of type \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e. The return data type of the aggregate is defined as the return type of this function. If \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e is not specified, then the ending state value is used as the aggregate's result, and the return type is \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003cp\u003eFor ordered-set (including hypothetical-set) aggregates, the final function receives not only the final state value, but also the values of all the direct arguments.\u003c/p\u003e\u003cp\u003eIf \u003ccode class=\"literal\"\u003eFINALFUNC_EXTRA\u003c/code\u003e is specified, then in addition to the final state value and any direct arguments, the final function receives extra NULL values corresponding to the aggregate's regular (aggregated) arguments. This is mainly useful to allow correct resolution of the aggregate result type when a polymorphic aggregate is being defined.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFINALFUNC_MODIFY\u003c/code\u003e = { \u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e | \u003ccode class=\"literal\"\u003eSHAREABLE\u003c/code\u003e | \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis option specifies whether the final function is a pure function that does not modify its arguments. \u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e indicates it does not; the other two values indicate that it may change the transition state value. See \u003ca href=\"/docs/18/sql-createaggregate.html#SQL-CREATEAGGREGATE-NOTES\" title=\"Notes\"\u003eNotes\u003c/a\u003e below for more detail. The default is \u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e, except for ordered-set aggregates, for which the default is \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e function may optionally be specified to allow the aggregate function to support partial aggregation. If provided, the \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e must combine two \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e values, each containing the result of aggregation over some subset of the input values, to produce a new \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e that represents the result of aggregating over both sets of inputs. This function can be thought of as an \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e, where instead of acting upon an individual input row and adding it to the running aggregate state, it adds another aggregate state to the running state.\u003c/p\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e must be declared as taking two arguments of the \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e and returning a value of the \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e. Optionally this function may be \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estrict\u003c/span\u003e”\u003c/span\u003e. In this case the function will not be called when either of the input states are null; the other state will be taken as the correct result.\u003c/p\u003e\u003cp\u003eFor aggregate functions whose \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e is \u003ccode class=\"type\"\u003einternal\u003c/code\u003e, the \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e must not be strict. In this case the \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e must ensure that null states are handled correctly and that the state being returned is properly stored in the aggregate memory context.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn aggregate function whose \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e is \u003ccode class=\"type\"\u003einternal\u003c/code\u003e can participate in parallel aggregation only if it has a \u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e function, which must serialize the aggregate state into a \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e value for transmission to another process. This function must take a single argument of type \u003ccode class=\"type\"\u003einternal\u003c/code\u003e and return type \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e. A corresponding \u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e is also required.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDeserialize a previously serialized aggregate state back into \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e. This function must take two arguments of types \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e and \u003ccode class=\"type\"\u003einternal\u003c/code\u003e, and produce a result of type \u003ccode class=\"type\"\u003einternal\u003c/code\u003e. (Note: the second, \u003ccode class=\"type\"\u003einternal\u003c/code\u003e argument is unused, but is required for type safety reasons.)\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe initial setting for the state value. This must be a string constant in the form accepted for the data type \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e. If not specified, the state value starts out null.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the forward state transition function to be called for each input row in moving-aggregate mode. This is exactly like the regular transition function, except that its first argument and result are of type \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e, which might be different from \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the inverse state transition function to be used in moving-aggregate mode. This function has the same argument and result types as \u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e, but it is used to remove a value from the current aggregate state, rather than add a value to it. The inverse transition function must have the same strictness attribute as the forward state transition function.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe data type for the aggregate's state value, when using moving-aggregate mode.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_size\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe approximate average size (in bytes) of the aggregate's state value, when using moving-aggregate mode. This works the same as \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the final function called to compute the aggregate's result after all input rows have been traversed, when using moving-aggregate mode. This works the same as \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e, except that its first argument's type is \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e and extra dummy arguments are specified by writing \u003ccode class=\"literal\"\u003eMFINALFUNC_EXTRA\u003c/code\u003e. The aggregate result type determined by \u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e or \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e must match that determined by the aggregate's regular implementation.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMFINALFUNC_MODIFY\u003c/code\u003e = { \u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e | \u003ccode class=\"literal\"\u003eSHAREABLE\u003c/code\u003e | \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis option is like \u003ccode class=\"literal\"\u003eFINALFUNC_MODIFY\u003c/code\u003e, but it describes the behavior of the moving-aggregate final function.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminitial_condition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe initial setting for the state value, when using moving-aggregate mode. This works the same as \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_operator\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe associated sort operator for a \u003ccode class=\"function\"\u003eMIN\u003c/code\u003e- or \u003ccode class=\"function\"\u003eMAX\u003c/code\u003e-like aggregate. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as the aggregate (which must be a single-argument normal aggregate).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ePARALLEL =\u003c/code\u003e { \u003ccode class=\"literal\"\u003eSAFE\u003c/code\u003e | \u003ccode class=\"literal\"\u003eRESTRICTED\u003c/code\u003e | \u003ccode class=\"literal\"\u003eUNSAFE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe meanings of \u003ccode class=\"literal\"\u003ePARALLEL SAFE\u003c/code\u003e, \u003ccode class=\"literal\"\u003ePARALLEL RESTRICTED\u003c/code\u003e, and \u003ccode class=\"literal\"\u003ePARALLEL UNSAFE\u003c/code\u003e are the same as in \u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003ccode class=\"command\"\u003eCREATE FUNCTION\u003c/code\u003e\u003c/a\u003e. An aggregate will not be considered for parallelization if it is marked \u003ccode class=\"literal\"\u003ePARALLEL UNSAFE\u003c/code\u003e (which is the default!) or \u003ccode class=\"literal\"\u003ePARALLEL RESTRICTED\u003c/code\u003e. Note that the parallel-safety markings of the aggregate's support functions are not consulted by the planner, only the marking of the aggregate itself.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eHYPOTHETICAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFor ordered-set aggregates only, this flag specifies that the aggregate arguments are to be processed according to the requirements for hypothetical-set aggregates: that is, the last few direct arguments must match the data types of the aggregated (\u003ccode class=\"literal\"\u003eWITHIN GROUP\u003c/code\u003e) arguments. The \u003ccode class=\"literal\"\u003eHYPOTHETICAL\u003c/code\u003e flag has no effect on run-time behavior, only on parse-time resolution of the data types and collations of the aggregate's arguments.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eThe parameters of \u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e can be written in any order, not just the order illustrated above.\u003c/p\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eIn parameters that specify support function names, you can write a schema name if needed, for example \u003ccode class=\"literal\"\u003eSFUNC = public.sum\u003c/code\u003e. Do not write argument types there, however — the argument types of the support functions are determined from other parameters.\u003c/p\u003e\u003cp\u003eOrdinarily, PostgreSQL functions are expected to be true functions that do not modify their input values. However, an aggregate transition function, \u003cspan class=\"emphasis\"\u003e\u003cem\u003ewhen used in the context of an aggregate\u003c/em\u003e\u003c/span\u003e, is allowed to cheat and modify its transition-state argument in place. This can provide substantial performance benefits compared to making a fresh copy of the transition state each time.\u003c/p\u003e\u003cp\u003eLikewise, while an aggregate final function is normally expected not to modify its input values, sometimes it is impractical to avoid modifying the transition-state argument. Such behavior must be declared using the \u003ccode class=\"literal\"\u003eFINALFUNC_MODIFY\u003c/code\u003e parameter. The \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e value indicates that the final function modifies the transition state in unspecified ways. This value prevents use of the aggregate as a window function, and it also prevents merging of transition states for aggregate calls that share the same input values and transition functions. The \u003ccode class=\"literal\"\u003eSHAREABLE\u003c/code\u003e value indicates that the transition function cannot be applied after the final function, but multiple final-function calls can be performed on the ending transition state value. This value prevents use of the aggregate as a window function, but it allows merging of transition states. (That is, the optimization of interest here is not applying the same final function repeatedly, but applying different final functions to the same ending transition state value. This is allowed as long as none of the final functions are marked \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e.)\u003c/p\u003e\u003cp\u003eIf an aggregate supports moving-aggregate mode, it will improve calculation efficiency when the aggregate is used as a window function for a window with moving frame start (that is, a frame start mode other than \u003ccode class=\"literal\"\u003eUNBOUNDED PRECEDING\u003c/code\u003e). Conceptually, the forward transition function adds input values to the aggregate's state when they enter the window frame from the bottom, and the inverse transition function removes them again when they leave the frame at the top. So, when values are removed, they are always removed in the same order they were added. Whenever the inverse transition function is invoked, it will thus receive the earliest added but not yet removed argument value(s). The inverse transition function can assume that at least one row will remain in the current state after it removes the oldest row. (When this would not be the case, the window function mechanism simply starts a fresh aggregation, rather than using the inverse transition function.)\u003c/p\u003e\u003cp\u003eThe forward transition function for moving-aggregate mode is not allowed to return NULL as the new state value. If the inverse transition function returns NULL, this is taken as an indication that the inverse function cannot reverse the state calculation for this particular input, and so the aggregate calculation will be redone from scratch for the current frame starting position. This convention allows moving-aggregate mode to be used in situations where there are some infrequent cases that are impractical to reverse out of the running state value.\u003c/p\u003e\u003cp\u003eIf no moving-aggregate implementation is supplied, the aggregate can still be used with moving frames, but \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e will recompute the whole aggregation whenever the start of the frame moves. Note that whether or not the aggregate supports moving-aggregate mode, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e can handle a moving frame end without recalculation; this is done by continuing to add new values to the aggregate's state. This is why use of an aggregate as a window function requires that the final function be read-only: it must not damage the aggregate's state value, so that the aggregation can be continued even after an aggregate result value has been obtained for one set of frame boundaries.\u003c/p\u003e\u003cp\u003eThe syntax for ordered-set aggregates allows \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e to be specified for both the last direct parameter and the last aggregated (\u003ccode class=\"literal\"\u003eWITHIN GROUP\u003c/code\u003e) parameter. However, the current implementation restricts use of \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e in two ways. First, ordered-set aggregates can only use \u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e, not other variadic array types. Second, if the last direct parameter is \u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e, then there can be only one aggregated parameter and it must also be \u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e. (In the representation used in the system catalogs, these two parameters are merged into a single \u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e item, since \u003ccode class=\"structname\"\u003epg_proc\u003c/code\u003e cannot represent functions with more than one \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e parameter.) If the aggregate is a hypothetical-set aggregate, the direct arguments that match the \u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e parameter are the hypothetical ones; any preceding parameters represent additional direct arguments that are not constrained to match the aggregated arguments.\u003c/p\u003e\u003cp\u003eCurrently, ordered-set aggregates do not need to support moving-aggregate mode, since they cannot be used as window functions.\u003c/p\u003e\u003cp\u003ePartial (including parallel) aggregation is currently not supported for ordered-set aggregates. Also, it will never be used for aggregate calls that include \u003ccode class=\"literal\"\u003eDISTINCT\u003c/code\u003e or \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clauses, since those semantics cannot be supported during partial aggregation.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eSee \u003ca href=\"/docs/18/xaggr.html\" title=\"36.12. User-Defined Aggregates\"\u003eSection 36.12\u003c/a\u003e.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e language extension. The SQL standard does not provide for user-defined aggregate functions.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-aggregate/?v=18\" title=\"ALTER AGGREGATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER AGGREGATE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-aggregate/?v=18\" title=\"DROP AGGREGATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP AGGREGATE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE [ OR REPLACE ] AGGREGATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ( [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e [ , ... ] ) (\n    SFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e,\n    STYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\n    [ , SSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC_EXTRA ]\n    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , COMBINEFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , SERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , DESERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , INITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MINVFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSTYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC_EXTRA ]\n    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , MINITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , SORTOP = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_operator\u003c/code\u003e\u003c/em\u003e ]\n    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n)\n\nCREATE [ OR REPLACE ] AGGREGATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ( [ [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e [ , ... ] ]\n                        ORDER BY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e [ , ... ] ) (\n    SFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e,\n    STYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\n    [ , SSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC_EXTRA ]\n    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , INITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n    [ , HYPOTHETICAL ]\n)\n\n\u003cspan class=\"phrase\"\u003eor the old syntax\u003c/span\u003e\n\nCREATE [ OR REPLACE ] AGGREGATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e (\n    BASETYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ebase_type\u003c/code\u003e\u003c/em\u003e,\n    SFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e,\n    STYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\n    [ , SSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC_EXTRA ]\n    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , COMBINEFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , SERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , DESERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , INITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MINVFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSTYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC_EXTRA ]\n    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , MINITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , SORTOP = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_operator\u003c/code\u003e\u003c/em\u003e ]\n)","synopsis_text":"CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n)\n\nCREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]\nORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , INITCOND = initial_condition ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n[ , HYPOTHETICAL ]\n)\n\nor the old syntax\n\nCREATE [ OR REPLACE ] AGGREGATE name (\nBASETYPE = base_type,\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n)"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-aggregate","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE AGGREGATE","Summary":"定义一个新的聚合函数","BodyHTML":"\u003cpre\u003eCREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n)\n\nCREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]\nORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , INITCOND = initial_condition ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n[ , HYPOTHETICAL ]\n)\n\n或者旧的语法\n\nCREATE [ OR REPLACE ] AGGREGATE name (\nBASETYPE = base_type,\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n)\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE AGGREGATE\u003c/code\u003e定义一个新的聚合函数。\u003ccode\u003eCREATE OR REPLACE AGGREGATE\u003c/code\u003e要么定义一个新的聚合函数，要么替换现有定义。发行版中包含一些基本且常用的聚合函数；其文档见\u003ca href=\"/docs/18/functions-aggregate.html\" rel=\"nofollow\"\u003e第 9.21 节\u003c/a\u003e。如果定义了新类型，或者需要尚未提供的聚合函数，则可以使用\u003ccode\u003eCREATE AGGREGATE\u003c/code\u003e提供所需特性。\u003c/p\u003e\u003cp\u003e在替换现有定义时，不得更改参数类型、结果类型和直接参数的数量。此外，新定义必须与旧定义属于同一种类（普通聚合、有序集聚合或假想集聚合）。\u003c/p\u003e\u003cp\u003e如果给出了一个模式名（例如\u003ccode\u003eCREATE AGGREGATE myschema.myagg ...\u003c/code\u003e），则该聚合函数会在指定模式中创建。否则会在当前模式中创建。\u003c/p\u003e\u003cp\u003e聚合函数由其名称和输入数据类型来标识。如果同一模式中的两个聚合作用于不同的输入类型，它们可以具有相同的名称。聚合的名称和输入数据类型还必须不同于同一模式中每个普通函数的名称和输入数据类型。这种行为与普通函数名的重载完全相同（见\u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e）。\u003c/p\u003e\u003cp\u003e简单聚合函数由一个或两个普通函数构成：一个状态转移函数 \u003cem\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e，以及一个可选的最终计算函数\u003cem\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e。其用法如下：\u003c/p\u003e\u003cpre\u003e\u003cem\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e( internal-state, next-data-values ) ---\u0026gt; next-internal-state\n\u003cem\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e( internal-state ) ---\u0026gt; aggregate-value\n\u003c/pre\u003e\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e会创建一个数据类型为 \u003cem\u003e\u003ccode\u003estype\u003c/code\u003e\u003c/em\u003e的临时变量，用来保存聚合的当前内部状态。对于每个输入行，都会先计算聚合参数值，然后以当前状态值和新的参数值调用状态转移函数，从而计算新的内部状态值。等到所有行都处理完毕后，再调用一次最终函数来计算聚合的返回值。如果没有最终函数，则原样返回结束时的状态值。\u003c/p\u003e\u003cp\u003e聚合函数可以提供一个初始条件，即内部状态值的初始值。它在数据库中以 \u003ccode\u003etext\u003c/code\u003e类型的值指定和存储，但它必须是该状态值数据类型常量的合法外部表示。如果未提供，则状态值初始为空值。\u003c/p\u003e\u003cp\u003e如果状态转移函数被声明为\u003cspan\u003e“\u003cspan\u003estrict\u003c/span\u003e”\u003c/span\u003e，则不能用空输入调用它。采用这种转移函数时，聚合执行的行为如下。任何输入值为空的行都会被忽略（不会调用该函数，并保留先前的状态值）。如果初始状态值为空，则在第一个所有输入值都非空的行上，第一个参数值会取代状态值，并在之后每一个所有输入值都非空的行上调用状态转移函数。这对于实现\u003ccode\u003emax\u003c/code\u003e之类的聚合很方便。注意，只有当 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e 与第一个 \u003cem\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e相同时，这种行为才可用。当这两种类型不同时，必须提供非空初始条件，或者使用非 strict 状态转移函数。\u003c/p\u003e\u003cp\u003e如果状态转移函数不是 strict，则它会无条件地在每个输入行上被调用，并且必须自行处理空输入和空状态值。这使聚合作者能够完全控制聚合对空值的处理方式。\u003c/p\u003e\u003cp\u003e如果最终函数被声明为\u003cspan\u003e“\u003cspan\u003estrict\u003c/span\u003e”\u003c/span\u003e，那么当结束状态值为空时不会调用它；而是自动返回空结果。（这当然只是 strict 函数的正常行为。）无论如何，最终函数都可以选择返回空值。例如，\u003ccode\u003eavg\u003c/code\u003e的最终函数在发现输入行数为零时会返回空值。\u003c/p\u003e\u003cp\u003e有时将最终函数声明为不仅接收状态值，还接收与聚合输入值相对应的额外参数会很有用。这样做的主要原因是，如果最终函数是多态的，状态值的数据类型不足以确定结果类型。这些额外参数总是以 NULL 传递（因此在使用 \u003ccode\u003eFINALFUNC_EXTRA\u003c/code\u003e选项时，最终函数不能声明为 strict），但它们仍然是有效参数。例如，最终函数可以利用 \u003ccode\u003eget_fn_expr_argtype\u003c/code\u003e来识别当前调用中的实际参数类型。\u003c/p\u003e\u003cp\u003e聚合可以选择支持\u003ca href=\"/docs/18/xaggr.html#XAGGR-MOVING-AGGREGATES\" rel=\"nofollow\"\u003e第 36.12.1 节\u003c/a\u003e中所述的\u003cem\u003e移动聚合模式\u003c/em\u003e。这要求指定 \u003ccode\u003eMSFUNC\u003c/code\u003e、\u003ccode\u003eMINVFUNC\u003c/code\u003e和 \u003ccode\u003eMSTYPE\u003c/code\u003e 参数，并可选指定 \u003ccode\u003eMSSPACE\u003c/code\u003e、\u003ccode\u003eMFINALFUNC\u003c/code\u003e、\u003ccode\u003eMFINALFUNC_EXTRA\u003c/code\u003e、\u003ccode\u003eMFINALFUNC_MODIFY\u003c/code\u003e 和\u003ccode\u003eMINITCOND\u003c/code\u003e 参数。除\u003ccode\u003eMINVFUNC\u003c/code\u003e外，这些参数的工作方式与对应的不带\u003ccode\u003eM\u003c/code\u003e的简单聚合参数相同；它们定义了该聚合的一种包含逆向状态转移函数的独立实现。\u003c/p\u003e\u003cp\u003e参数列表中带有\u003ccode\u003eORDER BY\u003c/code\u003e的语法会创建一种称为\u003cem\u003e有序集聚合\u003c/em\u003e的特殊聚合类型；如果指定了 \u003ccode\u003eHYPOTHETICAL\u003c/code\u003e，则会创建\u003cem\u003e假想集聚合\u003c/em\u003e。这些聚合以依赖顺序的方式对一组已排序的值进行操作，因此指定输入排序顺序是调用中必不可少的一部分。它们还可以有\u003cem\u003e直接\u003c/em\u003e参数，即每次聚合只求值一次而不是对每个输入行求值一次的参数。假想集聚合是有序集聚合的一个子类，其中要求某些直接参数在数量和数据类型上与被聚合的参数列匹配。这使得这些直接参数的值可以作为一条额外的\u003cspan\u003e“\u003cspan\u003e假想\u003c/span\u003e”\u003c/span\u003e行添加到聚合输入行的集合中。\u003c/p\u003e\u003cp\u003e聚合还可以选择支持\u003ca href=\"/docs/18/xaggr.html#XAGGR-PARTIAL-AGGREGATES\" rel=\"nofollow\"\u003e第 36.12.4 节\u003c/a\u003e中所述的\u003cem\u003e部分聚合\u003c/em\u003e。这要求指定 \u003ccode\u003eCOMBINEFUNC\u003c/code\u003e参数。如果 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e是 \u003ccode\u003einternal\u003c/code\u003e，通常还应提供\u003ccode\u003eSERIALFUNC\u003c/code\u003e和 \u003ccode\u003eDESERIALFUNC\u003c/code\u003e参数，以便能够进行并行聚合。注意，要启用并行聚合，该聚合还必须被标记为\u003ccode\u003ePARALLEL SAFE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e行为类似于\u003ccode\u003eMIN\u003c/code\u003e或\u003ccode\u003eMAX\u003c/code\u003e的聚合，有时可以通过查阅索引而不是扫描每个输入行来优化。如果该聚合可以这样优化，请通过指定一个\u003cem\u003e排序操作符\u003c/em\u003e来表明。基本要求是，该聚合必须返回该操作符所诱导的排序顺序中的第一个元素；换句话说：\u003c/p\u003e\u003cpre\u003eSELECT agg(col) FROM tab;\n\u003c/pre\u003e\u003cp\u003e必须等价于：\u003c/p\u003e\u003cpre\u003eSELECT col FROM tab ORDER BY col USING sortop LIMIT 1;\n\u003c/pre\u003e\u003cp\u003e进一步的假设是，该聚合忽略空输入，并且当且仅当不存在非空输入时返回空结果。通常，某种数据类型的\u003ccode\u003e\u0026lt;\u003c/code\u003e操作符是 \u003ccode\u003eMIN\u003c/code\u003e的合适排序操作符，而\u003ccode\u003e\u0026gt;\u003c/code\u003e是 \u003ccode\u003eMAX\u003c/code\u003e的合适排序操作符。注意，除非指定的操作符是 B-树索引操作符类中\u003cspan\u003e“\u003cspan\u003e小于\u003c/span\u003e”\u003c/span\u003e或\u003cspan\u003e“\u003cspan\u003e大于\u003c/span\u003e”\u003c/span\u003e策略成员，否则这种优化实际上永远不会生效。\u003c/p\u003e\u003cp\u003e要能够创建聚合函数，你必须在参数类型、状态类型和返回类型上拥有 \u003ccode\u003eUSAGE\u003c/code\u003e权限，并在支持函数上拥有 \u003ccode\u003eEXECUTE\u003c/code\u003e权限。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的聚合函数的名称（可带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e参数的模式：\u003ccode\u003eIN\u003c/code\u003e或\u003ccode\u003eVARIADIC\u003c/code\u003e。（聚合函数不支持\u003ccode\u003eOUT\u003c/code\u003e参数。）如果省略，默认为 \u003ccode\u003eIN\u003c/code\u003e。只有最后一个参数可以标记为 \u003ccode\u003eVARIADIC\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e参数的名称。目前这仅对文档有用。如果省略，则该参数没有名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该聚合函数所操作的输入数据类型。要创建零参数聚合函数，在参数说明列表的位置写\u003ccode\u003e*\u003c/code\u003e。（此类聚合的一个示例是 \u003ccode\u003ecount(*)\u003c/code\u003e。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ebase_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在\u003ccode\u003eCREATE AGGREGATE\u003c/code\u003e的旧语法中，输入数据类型通过 \u003ccode\u003ebasetype\u003c/code\u003e参数指定，而不是写在聚合名称旁边。注意，这种语法只允许一个输入参数。要用这种语法定义零参数聚合函数，应将 \u003ccode\u003ebasetype\u003c/code\u003e指定为\u003ccode\u003e\u0026#34;ANY\u0026#34;\u003c/code\u003e（不是 \u003ccode\u003e*\u003c/code\u003e）。有序集聚合不能用旧语法定义。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e对每个输入行调用的状态转移函数名称。对于普通的 \u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e 参数聚合函数，\u003cem\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e必须接收 \u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e+1 个参数，第一个参数类型为\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e，其余参数与该聚合声明的输入数据类型匹配。该函数必须返回 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e类型的值。它接收当前状态值和当前输入数据值，并返回下一个状态值。\u003c/p\u003e\u003cp\u003e对于有序集（包括假想集）聚合，状态转移函数只接收当前状态值和聚合参数，不接收直接参数。除此之外它与普通情况相同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e聚合状态值的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e聚合状态值的近似平均大小（以字节为单位）。如果省略该参数或其值为零，则会基于\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e使用默认估计值。规划器使用这个值来估计分组聚合查询所需的内存。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在遍历完所有输入行后调用、用于计算聚合结果的最终函数名称。对于普通聚合，该函数必须只接收一个类型为\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e的参数。聚合的返回数据类型定义为该函数的返回类型。如果未指定 \u003cem\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e，则结束状态值会被用作聚合结果，返回类型也就是\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003cp\u003e对于有序集（包括假想集）聚合，最终函数不仅接收最终状态值，还会接收所有直接参数的值。\u003c/p\u003e\u003cp\u003e如果指定了\u003ccode\u003eFINALFUNC_EXTRA\u003c/code\u003e，则除了最终状态值和任何直接参数之外，最终函数还会接收额外的 NULL 值，它们对应于该聚合的常规（被聚合的）参数。这主要用于在定义多态聚合时能够正确解析聚合的结果类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFINALFUNC_MODIFY\u003c/code\u003e = { \u003ccode\u003eREAD_ONLY\u003c/code\u003e | \u003ccode\u003eSHAREABLE\u003c/code\u003e | \u003ccode\u003eREAD_WRITE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e此选项指定最终函数是否为不修改其参数的纯函数。\u003ccode\u003eREAD_ONLY\u003c/code\u003e表示不会修改；另外两个值表示它可能会更改状态值。更多细节见下文\u003ca href=\"/docs/18/sql-createaggregate.html#SQL-CREATEAGGREGATE-NOTES\" title=\"注解\" rel=\"nofollow\"\u003e注解\u003c/a\u003e。默认值是\u003ccode\u003eREAD_ONLY\u003c/code\u003e，但对于有序集聚合，默认值是 \u003ccode\u003eREAD_WRITE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可以选择指定\u003cem\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e 函数，以使聚合函数支持部分聚合。如果提供了它，则 \u003cem\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e必须把两个 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e值合并起来；这两个值各自包含对某个输入值子集聚合得到的结果，并产生一个新的 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e，表示同时对这两组输入进行聚合的结果。可以把这个函数看作一种 \u003cem\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e：不同之处在于，它不是处理单个输入行并把它加入当前聚合状态，而是把另一个聚合状态加入当前状态。\u003c/p\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e必须声明为接收两个\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e 参数，并返回一个\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e 值。该函数也可以选择声明为\u003cspan\u003e“\u003cspan\u003estrict\u003c/span\u003e”\u003c/span\u003e。在这种情况下，只要任一输入状态为空，就不会调用该函数；另一个状态会被视为正确结果。\u003c/p\u003e\u003cp\u003e对于\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e为 \u003ccode\u003einternal\u003c/code\u003e的聚合函数，\u003cem\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e不能声明为 strict。在这种情况下，\u003cem\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e必须确保正确处理空状态，并且返回的状态被正确存储在聚合内存上下文中。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e为 \u003ccode\u003einternal\u003c/code\u003e的聚合函数，只有在具备一个 \u003cem\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e函数时才能参与并行聚合；该函数必须把聚合状态序列化为一个\u003ccode\u003ebytea\u003c/code\u003e值，以传输给另一个进程。该函数必须接收一个\u003ccode\u003einternal\u003c/code\u003e类型参数并返回 \u003ccode\u003ebytea\u003c/code\u003e类型结果。还需要相应的 \u003cem\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将先前序列化的聚合状态反序列化回 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e。该函数必须接收\u003ccode\u003ebytea\u003c/code\u003e和\u003ccode\u003einternal\u003c/code\u003e两个参数，并产生一个\u003ccode\u003einternal\u003c/code\u003e类型的结果。（注意：第二个\u003ccode\u003einternal\u003c/code\u003e 参数未被使用，但出于类型安全的原因必须存在。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e状态值的初始设置。这必须是一个字符串常量，其形式必须能被数据类型 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e接受。如果未指定，状态值初始为空值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在移动聚合模式下，对每个输入行调用的前向状态转移函数名称。它与常规转移函数完全相同，只是其第一个参数和结果都是 \u003cem\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e类型，这可能与 \u003cem\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e不同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eminvfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在移动聚合模式中使用的逆向状态转移函数名称。该函数的参数和结果类型与 \u003cem\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e相同，但它不是把值添加到当前聚合状态中，而是从中移除一个值。逆向状态转移函数必须与前向状态转移函数具有相同的 strict 属性。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用移动聚合模式时，聚合状态值的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003emstate_data_size\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用移动聚合模式时，聚合状态值的近似平均大小（以字节为单位）。其作用与\u003cem\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e相同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在使用移动聚合模式时，在遍历完所有输入行后调用、用于计算聚合结果的最终函数名称。其工作方式与\u003cem\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e相同，只是其第一个参数的类型是\u003cem\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e，而额外的哑参数通过写\u003ccode\u003eMFINALFUNC_EXTRA\u003c/code\u003e来指定。由 \u003cem\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e或 \u003cem\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e决定的聚合结果类型，必须与该聚合常规实现决定的结果类型一致。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eMFINALFUNC_MODIFY\u003c/code\u003e = { \u003ccode\u003eREAD_ONLY\u003c/code\u003e | \u003ccode\u003eSHAREABLE\u003c/code\u003e | \u003ccode\u003eREAD_WRITE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e此选项类似于\u003ccode\u003eFINALFUNC_MODIFY\u003c/code\u003e，只是它描述了移动聚合最终函数的行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eminitial_condition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用移动聚合模式时，状态值的初始设置。其作用与 \u003cem\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e相同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003esort_operator\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e与类\u003ccode\u003eMIN\u003c/code\u003e或类\u003ccode\u003eMAX\u003c/code\u003e聚合关联的排序操作符。这只是一个操作符名称（可带模式限定）。假定该操作符与该聚合具有相同的输入数据类型（该聚合必须是单参数普通聚合）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003ePARALLEL =\u003c/code\u003e { \u003ccode\u003eSAFE\u003c/code\u003e | \u003ccode\u003eRESTRICTED\u003c/code\u003e | \u003ccode\u003eUNSAFE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003ePARALLEL SAFE\u003c/code\u003e、\u003ccode\u003ePARALLEL RESTRICTED\u003c/code\u003e和\u003ccode\u003ePARALLEL UNSAFE\u003c/code\u003e的含义与 \u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\" rel=\"nofollow\"\u003e\u003ccode\u003eCREATE FUNCTION\u003c/code\u003e\u003c/a\u003e中的含义相同。如果一个聚合被标记为 \u003ccode\u003ePARALLEL UNSAFE\u003c/code\u003e（这正是默认值！）或者 \u003ccode\u003ePARALLEL RESTRICTED\u003c/code\u003e，就不会考虑将它并行化。注意，规划器不会查阅聚合支持函数的并行安全标记，它只看聚合本身的标记。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eHYPOTHETICAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e仅用于有序集聚合。该标志指定应按假想集聚合的要求处理聚合参数：也就是，最后几个直接参数必须与聚合（\u003ccode\u003eWITHIN GROUP\u003c/code\u003e）参数的数据类型匹配。\u003ccode\u003eHYPOTHETICAL\u003c/code\u003e标志不会影响运行时行为，只影响在解析时对聚合参数的数据类型和排序规则的确定。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e\u003ccode\u003eCREATE AGGREGATE\u003c/code\u003e的参数可以按任意顺序书写，而不只是按上面展示的顺序。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e在指定支持函数名的参数中，如果需要可以写模式名，例如 \u003ccode\u003eSFUNC = public.sum\u003c/code\u003e。但是不要在那里写参数类型 — 支持函数的参数类型由其他参数决定。\u003c/p\u003e\u003cp\u003e通常，PostgreSQL 函数应当是真正不修改其输入值的函数。然而，聚合的状态转移函数\u003cspan\u003e\u003cem\u003e在聚合上下文中使用时\u003c/em\u003e\u003c/span\u003e，允许取巧地就地修改其状态值参数。与每次都新建一份状态值副本相比，这样可能带来显著的性能收益。\u003c/p\u003e\u003cp\u003e同样，虽然通常也期望聚合最终函数不修改其输入值，但有时无法避免修改状态值参数。这种行为必须通过\u003ccode\u003eFINALFUNC_MODIFY\u003c/code\u003e参数声明。\u003ccode\u003eREAD_WRITE\u003c/code\u003e值表示最终函数会以未指定的方式修改状态值。这个值会阻止将该聚合用作窗口函数，也会阻止对共享相同输入值和状态转移函数的聚合调用合并状态值。\u003ccode\u003eSHAREABLE\u003c/code\u003e值表示在调用最终函数之后，不能再应用状态转移函数，但可以对结束时的状态值执行多次最终函数调用。这个值同样会阻止将该聚合用作窗口函数，但允许合并状态值。（也就是说，这里关注的优化不是重复应用同一个最终函数，而是把不同的最终函数应用到同一个结束状态值上。只要这些最终函数都没有标记为\u003ccode\u003eREAD_WRITE\u003c/code\u003e，这就是允许的。）\u003c/p\u003e\u003cp\u003e如果一个聚合支持移动聚合模式，那么当它被用作具有移动帧起点的窗口的窗口函数时（即帧起点模式不是\u003ccode\u003eUNBOUNDED PRECEDING\u003c/code\u003e），就能提高计算效率。从概念上讲，前向状态转移函数在输入值从底部进入窗口帧时把它们加入聚合状态，逆向状态转移函数则在这些值从顶部离开窗口帧时再次将其移除。因此，移除值时，总是按它们被加入时的相同顺序移除。每当调用逆向状态转移函数时，它收到的就是最早被加入但尚未被移除的参数值。逆向状态转移函数可以假定，在它移除最旧的那一行之后，当前状态中至少还会保留一行。（若非如此，窗口函数机制就会简单地重新开始一次全新的聚合，而不是使用逆向状态转移函数。）\u003c/p\u003e\u003cp\u003e用于移动聚合模式的前向状态转移函数不允许返回 NULL 作为新的状态值。如果逆向状态转移函数返回 NULL，就表示该逆向函数无法针对这个特定输入逆转状态计算，因此会从当前帧起始位置开始重新计算聚合。这一约定使得移动聚合模式可用于这样一些场景：在少数不常见的情况下，难以从当前状态值中逆向消除对应输入的影响。\u003c/p\u003e\u003cp\u003e如果没有提供移动聚合实现，聚合仍然可以与移动帧一起使用，但每当帧起点移动时，\u003cspan\u003ePostgreSQL\u003c/span\u003e都会重新计算整个聚合。注意，无论聚合是否支持移动聚合模式，\u003cspan\u003ePostgreSQL\u003c/span\u003e 都能在不重新计算的情况下处理移动的帧结束位置；做法是继续把新值添加到聚合状态中。这就是为什么将聚合用作窗口函数时要求最终函数必须是只读的：它不能损坏聚合的状态值，这样即使已经针对一组帧边界得到了聚合结果值，聚合也能继续进行。\u003c/p\u003e\u003cp\u003e有序集聚合的语法允许对最后一个直接参数和最后一个聚合（\u003ccode\u003eWITHIN GROUP\u003c/code\u003e）参数都指定 \u003ccode\u003eVARIADIC\u003c/code\u003e。但是，当前实现从两个方面限制了 \u003ccode\u003eVARIADIC\u003c/code\u003e的使用。第一，有序集聚合只能使用 \u003ccode\u003eVARIADIC \u0026#34;any\u0026#34;\u003c/code\u003e，不能使用其他可变参数数组类型。第二，如果最后一个直接参数是\u003ccode\u003eVARIADIC \u0026#34;any\u0026#34;\u003c/code\u003e，则只能有一个聚合参数，并且它也必须是\u003ccode\u003eVARIADIC \u0026#34;any\u0026#34;\u003c/code\u003e。（在系统目录使用的表示中，这两个参数会合并为单个 \u003ccode\u003eVARIADIC \u0026#34;any\u0026#34;\u003c/code\u003e项，因为\u003ccode\u003epg_proc\u003c/code\u003e 无法表示具有多个\u003ccode\u003eVARIADIC\u003c/code\u003e参数的函数。）如果该聚合是假想集聚合，则与\u003ccode\u003eVARIADIC \u0026#34;any\u0026#34;\u003c/code\u003e参数匹配的直接参数就是假想参数；其前面的任何参数都表示额外的直接参数，它们不受必须与聚合参数匹配的约束。\u003c/p\u003e\u003cp\u003e当前，有序集聚合无需支持移动聚合模式，因为它们不能被用作窗口函数。\u003c/p\u003e\u003cp\u003e部分（包括并行）聚合当前不支持有序集聚合。此外，对于包含 \u003ccode\u003eDISTINCT\u003c/code\u003e或\u003ccode\u003eORDER BY\u003c/code\u003e子句的聚合调用，也永远不会使用部分聚合，因为在部分聚合期间无法支持这些语义。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e见\u003ca href=\"/docs/18/xaggr.html\" rel=\"nofollow\"\u003e第 36.12 节\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE AGGREGATE\u003c/code\u003e是 \u003cspan\u003ePostgreSQL\u003c/span\u003e的语言扩展。SQL 标准不提供用户定义聚合函数。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-aggregate/?v=18\" title=\"ALTER AGGREGATE\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER AGGREGATE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-aggregate/?v=18\" title=\"DROP AGGREGATE\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP AGGREGATE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"c8f6b5163331d81b5f418bc893d14b07b9c34b9d689b5562e2f660c756dc1f65","Payload":{"purpose_zh":"定义一个新的聚合函数","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e定义一个新的聚合函数。\u003ccode class=\"command\"\u003eCREATE OR REPLACE AGGREGATE\u003c/code\u003e要么定义一个新的聚合函数，要么替换现有定义。发行版中包含一些基本且常用的聚合函数；其文档见\u003ca href=\"/docs/18/functions-aggregate.html\" title=\"9.21. 聚合函数\"\u003e第 9.21 节\u003c/a\u003e。如果定义了新类型，或者需要尚未提供的聚合函数，则可以使用\u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e提供所需特性。\u003c/p\u003e\u003cp\u003e在替换现有定义时，不得更改参数类型、结果类型和直接参数的数量。此外，新定义必须与旧定义属于同一种类（普通聚合、有序集聚合或假想集聚合）。\u003c/p\u003e\u003cp\u003e如果给出了一个模式名（例如\u003ccode class=\"literal\"\u003eCREATE AGGREGATE myschema.myagg ...\u003c/code\u003e），则该聚合函数会在指定模式中创建。否则会在当前模式中创建。\u003c/p\u003e\u003cp\u003e聚合函数由其名称和输入数据类型来标识。如果同一模式中的两个聚合作用于不同的输入类型，它们可以具有相同的名称。聚合的名称和输入数据类型还必须不同于同一模式中每个普通函数的名称和输入数据类型。这种行为与普通函数名的重载完全相同（见\u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e）。\u003c/p\u003e\u003cp\u003e简单聚合函数由一个或两个普通函数构成：一个状态转移函数 \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e，以及一个可选的最终计算函数\u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e。其用法如下：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e( internal-state, next-data-values ) ---\u0026gt; next-internal-state\n\u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e( internal-state ) ---\u0026gt; aggregate-value\n\u003c/pre\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e会创建一个数据类型为 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estype\u003c/code\u003e\u003c/em\u003e的临时变量，用来保存聚合的当前内部状态。对于每个输入行，都会先计算聚合参数值，然后以当前状态值和新的参数值调用状态转移函数，从而计算新的内部状态值。等到所有行都处理完毕后，再调用一次最终函数来计算聚合的返回值。如果没有最终函数，则原样返回结束时的状态值。\u003c/p\u003e\u003cp\u003e聚合函数可以提供一个初始条件，即内部状态值的初始值。它在数据库中以 \u003ccode class=\"type\"\u003etext\u003c/code\u003e类型的值指定和存储，但它必须是该状态值数据类型常量的合法外部表示。如果未提供，则状态值初始为空值。\u003c/p\u003e\u003cp\u003e如果状态转移函数被声明为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estrict\u003c/span\u003e”\u003c/span\u003e，则不能用空输入调用它。采用这种转移函数时，聚合执行的行为如下。任何输入值为空的行都会被忽略（不会调用该函数，并保留先前的状态值）。如果初始状态值为空，则在第一个所有输入值都非空的行上，第一个参数值会取代状态值，并在之后每一个所有输入值都非空的行上调用状态转移函数。这对于实现\u003ccode class=\"function\"\u003emax\u003c/code\u003e之类的聚合很方便。注意，只有当 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e 与第一个 \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e相同时，这种行为才可用。当这两种类型不同时，必须提供非空初始条件，或者使用非 strict 状态转移函数。\u003c/p\u003e\u003cp\u003e如果状态转移函数不是 strict，则它会无条件地在每个输入行上被调用，并且必须自行处理空输入和空状态值。这使聚合作者能够完全控制聚合对空值的处理方式。\u003c/p\u003e\u003cp\u003e如果最终函数被声明为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estrict\u003c/span\u003e”\u003c/span\u003e，那么当结束状态值为空时不会调用它；而是自动返回空结果。（这当然只是 strict 函数的正常行为。）无论如何，最终函数都可以选择返回空值。例如，\u003ccode class=\"function\"\u003eavg\u003c/code\u003e的最终函数在发现输入行数为零时会返回空值。\u003c/p\u003e\u003cp\u003e有时将最终函数声明为不仅接收状态值，还接收与聚合输入值相对应的额外参数会很有用。这样做的主要原因是，如果最终函数是多态的，状态值的数据类型不足以确定结果类型。这些额外参数总是以 NULL 传递（因此在使用 \u003ccode class=\"literal\"\u003eFINALFUNC_EXTRA\u003c/code\u003e选项时，最终函数不能声明为 strict），但它们仍然是有效参数。例如，最终函数可以利用 \u003ccode class=\"function\"\u003eget_fn_expr_argtype\u003c/code\u003e来识别当前调用中的实际参数类型。\u003c/p\u003e\u003cp\u003e聚合可以选择支持\u003ca href=\"/docs/18/xaggr.html#XAGGR-MOVING-AGGREGATES\" title=\"36.12.1. 移动聚合模式\"\u003e第 36.12.1 节\u003c/a\u003e中所述的\u003cem class=\"firstterm\"\u003e移动聚合模式\u003c/em\u003e。这要求指定 \u003ccode class=\"literal\"\u003eMSFUNC\u003c/code\u003e、\u003ccode class=\"literal\"\u003eMINVFUNC\u003c/code\u003e和 \u003ccode class=\"literal\"\u003eMSTYPE\u003c/code\u003e 参数，并可选指定 \u003ccode class=\"literal\"\u003eMSSPACE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eMFINALFUNC\u003c/code\u003e、\u003ccode class=\"literal\"\u003eMFINALFUNC_EXTRA\u003c/code\u003e、\u003ccode class=\"literal\"\u003eMFINALFUNC_MODIFY\u003c/code\u003e 和\u003ccode class=\"literal\"\u003eMINITCOND\u003c/code\u003e 参数。除\u003ccode class=\"literal\"\u003eMINVFUNC\u003c/code\u003e外，这些参数的工作方式与对应的不带\u003ccode class=\"literal\"\u003eM\u003c/code\u003e的简单聚合参数相同；它们定义了该聚合的一种包含逆向状态转移函数的独立实现。\u003c/p\u003e\u003cp\u003e参数列表中带有\u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e的语法会创建一种称为\u003cem class=\"firstterm\"\u003e有序集聚合\u003c/em\u003e的特殊聚合类型；如果指定了 \u003ccode class=\"literal\"\u003eHYPOTHETICAL\u003c/code\u003e，则会创建\u003cem class=\"firstterm\"\u003e假想集聚合\u003c/em\u003e。这些聚合以依赖顺序的方式对一组已排序的值进行操作，因此指定输入排序顺序是调用中必不可少的一部分。它们还可以有\u003cem class=\"firstterm\"\u003e直接\u003c/em\u003e参数，即每次聚合只求值一次而不是对每个输入行求值一次的参数。假想集聚合是有序集聚合的一个子类，其中要求某些直接参数在数量和数据类型上与被聚合的参数列匹配。这使得这些直接参数的值可以作为一条额外的\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e假想\u003c/span\u003e”\u003c/span\u003e行添加到聚合输入行的集合中。\u003c/p\u003e\u003cp\u003e聚合还可以选择支持\u003ca href=\"/docs/18/xaggr.html#XAGGR-PARTIAL-AGGREGATES\" title=\"36.12.4. 部分聚合\"\u003e第 36.12.4 节\u003c/a\u003e中所述的\u003cem class=\"firstterm\"\u003e部分聚合\u003c/em\u003e。这要求指定 \u003ccode class=\"literal\"\u003eCOMBINEFUNC\u003c/code\u003e参数。如果 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e是 \u003ccode class=\"type\"\u003einternal\u003c/code\u003e，通常还应提供\u003ccode class=\"literal\"\u003eSERIALFUNC\u003c/code\u003e和 \u003ccode class=\"literal\"\u003eDESERIALFUNC\u003c/code\u003e参数，以便能够进行并行聚合。注意，要启用并行聚合，该聚合还必须被标记为\u003ccode class=\"literal\"\u003ePARALLEL SAFE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e行为类似于\u003ccode class=\"function\"\u003eMIN\u003c/code\u003e或\u003ccode class=\"function\"\u003eMAX\u003c/code\u003e的聚合，有时可以通过查阅索引而不是扫描每个输入行来优化。如果该聚合可以这样优化，请通过指定一个\u003cem class=\"firstterm\"\u003e排序操作符\u003c/em\u003e来表明。基本要求是，该聚合必须返回该操作符所诱导的排序顺序中的第一个元素；换句话说：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT agg(col) FROM tab;\n\u003c/pre\u003e\u003cp\u003e必须等价于：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT col FROM tab ORDER BY col USING sortop LIMIT 1;\n\u003c/pre\u003e\u003cp\u003e进一步的假设是，该聚合忽略空输入，并且当且仅当不存在非空输入时返回空结果。通常，某种数据类型的\u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e操作符是 \u003ccode class=\"function\"\u003eMIN\u003c/code\u003e的合适排序操作符，而\u003ccode class=\"literal\"\u003e\u0026gt;\u003c/code\u003e是 \u003ccode class=\"function\"\u003eMAX\u003c/code\u003e的合适排序操作符。注意，除非指定的操作符是 B-树索引操作符类中\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e小于\u003c/span\u003e”\u003c/span\u003e或\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e大于\u003c/span\u003e”\u003c/span\u003e策略成员，否则这种优化实际上永远不会生效。\u003c/p\u003e\u003cp\u003e要能够创建聚合函数，你必须在参数类型、状态类型和返回类型上拥有 \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e权限，并在支持函数上拥有 \u003ccode class=\"literal\"\u003eEXECUTE\u003c/code\u003e权限。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的聚合函数的名称（可带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e参数的模式：\u003ccode class=\"literal\"\u003eIN\u003c/code\u003e或\u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e。（聚合函数不支持\u003ccode class=\"literal\"\u003eOUT\u003c/code\u003e参数。）如果省略，默认为 \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e。只有最后一个参数可以标记为 \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e参数的名称。目前这仅对文档有用。如果省略，则该参数没有名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该聚合函数所操作的输入数据类型。要创建零参数聚合函数，在参数说明列表的位置写\u003ccode class=\"literal\"\u003e*\u003c/code\u003e。（此类聚合的一个示例是 \u003ccode class=\"function\"\u003ecount(*)\u003c/code\u003e。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ebase_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在\u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e的旧语法中，输入数据类型通过 \u003ccode class=\"literal\"\u003ebasetype\u003c/code\u003e参数指定，而不是写在聚合名称旁边。注意，这种语法只允许一个输入参数。要用这种语法定义零参数聚合函数，应将 \u003ccode class=\"literal\"\u003ebasetype\u003c/code\u003e指定为\u003ccode class=\"literal\"\u003e\"ANY\"\u003c/code\u003e（不是 \u003ccode class=\"literal\"\u003e*\u003c/code\u003e）。有序集聚合不能用旧语法定义。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e对每个输入行调用的状态转移函数名称。对于普通的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e 参数聚合函数，\u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e必须接收 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e+1 个参数，第一个参数类型为\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e，其余参数与该聚合声明的输入数据类型匹配。该函数必须返回 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e类型的值。它接收当前状态值和当前输入数据值，并返回下一个状态值。\u003c/p\u003e\u003cp\u003e对于有序集（包括假想集）聚合，状态转移函数只接收当前状态值和聚合参数，不接收直接参数。除此之外它与普通情况相同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e聚合状态值的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e聚合状态值的近似平均大小（以字节为单位）。如果省略该参数或其值为零，则会基于\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e使用默认估计值。规划器使用这个值来估计分组聚合查询所需的内存。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在遍历完所有输入行后调用、用于计算聚合结果的最终函数名称。对于普通聚合，该函数必须只接收一个类型为\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e的参数。聚合的返回数据类型定义为该函数的返回类型。如果未指定 \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e，则结束状态值会被用作聚合结果，返回类型也就是\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003cp\u003e对于有序集（包括假想集）聚合，最终函数不仅接收最终状态值，还会接收所有直接参数的值。\u003c/p\u003e\u003cp\u003e如果指定了\u003ccode class=\"literal\"\u003eFINALFUNC_EXTRA\u003c/code\u003e，则除了最终状态值和任何直接参数之外，最终函数还会接收额外的 NULL 值，它们对应于该聚合的常规（被聚合的）参数。这主要用于在定义多态聚合时能够正确解析聚合的结果类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFINALFUNC_MODIFY\u003c/code\u003e = { \u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e | \u003ccode class=\"literal\"\u003eSHAREABLE\u003c/code\u003e | \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e此选项指定最终函数是否为不修改其参数的纯函数。\u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e表示不会修改；另外两个值表示它可能会更改状态值。更多细节见下文\u003ca href=\"/docs/18/sql-createaggregate.html#SQL-CREATEAGGREGATE-NOTES\" title=\"注解\"\u003e注解\u003c/a\u003e。默认值是\u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e，但对于有序集聚合，默认值是 \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可以选择指定\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e 函数，以使聚合函数支持部分聚合。如果提供了它，则 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e必须把两个 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e值合并起来；这两个值各自包含对某个输入值子集聚合得到的结果，并产生一个新的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e，表示同时对这两组输入进行聚合的结果。可以把这个函数看作一种 \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e：不同之处在于，它不是处理单个输入行并把它加入当前聚合状态，而是把另一个聚合状态加入当前状态。\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e必须声明为接收两个\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e 参数，并返回一个\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e 值。该函数也可以选择声明为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estrict\u003c/span\u003e”\u003c/span\u003e。在这种情况下，只要任一输入状态为空，就不会调用该函数；另一个状态会被视为正确结果。\u003c/p\u003e\u003cp\u003e对于\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e为 \u003ccode class=\"type\"\u003einternal\u003c/code\u003e的聚合函数，\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e不能声明为 strict。在这种情况下，\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e必须确保正确处理空状态，并且返回的状态被正确存储在聚合内存上下文中。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e为 \u003ccode class=\"type\"\u003einternal\u003c/code\u003e的聚合函数，只有在具备一个 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e函数时才能参与并行聚合；该函数必须把聚合状态序列化为一个\u003ccode class=\"type\"\u003ebytea\u003c/code\u003e值，以传输给另一个进程。该函数必须接收一个\u003ccode class=\"type\"\u003einternal\u003c/code\u003e类型参数并返回 \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e类型结果。还需要相应的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将先前序列化的聚合状态反序列化回 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e。该函数必须接收\u003ccode class=\"type\"\u003ebytea\u003c/code\u003e和\u003ccode class=\"type\"\u003einternal\u003c/code\u003e两个参数，并产生一个\u003ccode class=\"type\"\u003einternal\u003c/code\u003e类型的结果。（注意：第二个\u003ccode class=\"type\"\u003einternal\u003c/code\u003e 参数未被使用，但出于类型安全的原因必须存在。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e状态值的初始设置。这必须是一个字符串常量，其形式必须能被数据类型 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e接受。如果未指定，状态值初始为空值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在移动聚合模式下，对每个输入行调用的前向状态转移函数名称。它与常规转移函数完全相同，只是其第一个参数和结果都是 \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e类型，这可能与 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e不同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvfunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在移动聚合模式中使用的逆向状态转移函数名称。该函数的参数和结果类型与 \u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e相同，但它不是把值添加到当前聚合状态中，而是从中移除一个值。逆向状态转移函数必须与前向状态转移函数具有相同的 strict 属性。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用移动聚合模式时，聚合状态值的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_size\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用移动聚合模式时，聚合状态值的近似平均大小（以字节为单位）。其作用与\u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e相同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在使用移动聚合模式时，在遍历完所有输入行后调用、用于计算聚合结果的最终函数名称。其工作方式与\u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e相同，只是其第一个参数的类型是\u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e，而额外的哑参数通过写\u003ccode class=\"literal\"\u003eMFINALFUNC_EXTRA\u003c/code\u003e来指定。由 \u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e或 \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e决定的聚合结果类型，必须与该聚合常规实现决定的结果类型一致。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMFINALFUNC_MODIFY\u003c/code\u003e = { \u003ccode class=\"literal\"\u003eREAD_ONLY\u003c/code\u003e | \u003ccode class=\"literal\"\u003eSHAREABLE\u003c/code\u003e | \u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e此选项类似于\u003ccode class=\"literal\"\u003eFINALFUNC_MODIFY\u003c/code\u003e，只是它描述了移动聚合最终函数的行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminitial_condition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用移动聚合模式时，状态值的初始设置。其作用与 \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e相同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_operator\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e与类\u003ccode class=\"function\"\u003eMIN\u003c/code\u003e或类\u003ccode class=\"function\"\u003eMAX\u003c/code\u003e聚合关联的排序操作符。这只是一个操作符名称（可带模式限定）。假定该操作符与该聚合具有相同的输入数据类型（该聚合必须是单参数普通聚合）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ePARALLEL =\u003c/code\u003e { \u003ccode class=\"literal\"\u003eSAFE\u003c/code\u003e | \u003ccode class=\"literal\"\u003eRESTRICTED\u003c/code\u003e | \u003ccode class=\"literal\"\u003eUNSAFE\u003c/code\u003e }\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003ePARALLEL SAFE\u003c/code\u003e、\u003ccode class=\"literal\"\u003ePARALLEL RESTRICTED\u003c/code\u003e和\u003ccode class=\"literal\"\u003ePARALLEL UNSAFE\u003c/code\u003e的含义与 \u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003ccode class=\"command\"\u003eCREATE FUNCTION\u003c/code\u003e\u003c/a\u003e中的含义相同。如果一个聚合被标记为 \u003ccode class=\"literal\"\u003ePARALLEL UNSAFE\u003c/code\u003e（这正是默认值！）或者 \u003ccode class=\"literal\"\u003ePARALLEL RESTRICTED\u003c/code\u003e，就不会考虑将它并行化。注意，规划器不会查阅聚合支持函数的并行安全标记，它只看聚合本身的标记。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eHYPOTHETICAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e仅用于有序集聚合。该标志指定应按假想集聚合的要求处理聚合参数：也就是，最后几个直接参数必须与聚合（\u003ccode class=\"literal\"\u003eWITHIN GROUP\u003c/code\u003e）参数的数据类型匹配。\u003ccode class=\"literal\"\u003eHYPOTHETICAL\u003c/code\u003e标志不会影响运行时行为，只影响在解析时对聚合参数的数据类型和排序规则的确定。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e的参数可以按任意顺序书写，而不只是按上面展示的顺序。\u003c/p\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e在指定支持函数名的参数中，如果需要可以写模式名，例如 \u003ccode class=\"literal\"\u003eSFUNC = public.sum\u003c/code\u003e。但是不要在那里写参数类型 — 支持函数的参数类型由其他参数决定。\u003c/p\u003e\u003cp\u003e通常，PostgreSQL 函数应当是真正不修改其输入值的函数。然而，聚合的状态转移函数\u003cspan class=\"emphasis\"\u003e\u003cem\u003e在聚合上下文中使用时\u003c/em\u003e\u003c/span\u003e，允许取巧地就地修改其状态值参数。与每次都新建一份状态值副本相比，这样可能带来显著的性能收益。\u003c/p\u003e\u003cp\u003e同样，虽然通常也期望聚合最终函数不修改其输入值，但有时无法避免修改状态值参数。这种行为必须通过\u003ccode class=\"literal\"\u003eFINALFUNC_MODIFY\u003c/code\u003e参数声明。\u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e值表示最终函数会以未指定的方式修改状态值。这个值会阻止将该聚合用作窗口函数，也会阻止对共享相同输入值和状态转移函数的聚合调用合并状态值。\u003ccode class=\"literal\"\u003eSHAREABLE\u003c/code\u003e值表示在调用最终函数之后，不能再应用状态转移函数，但可以对结束时的状态值执行多次最终函数调用。这个值同样会阻止将该聚合用作窗口函数，但允许合并状态值。（也就是说，这里关注的优化不是重复应用同一个最终函数，而是把不同的最终函数应用到同一个结束状态值上。只要这些最终函数都没有标记为\u003ccode class=\"literal\"\u003eREAD_WRITE\u003c/code\u003e，这就是允许的。）\u003c/p\u003e\u003cp\u003e如果一个聚合支持移动聚合模式，那么当它被用作具有移动帧起点的窗口的窗口函数时（即帧起点模式不是\u003ccode class=\"literal\"\u003eUNBOUNDED PRECEDING\u003c/code\u003e），就能提高计算效率。从概念上讲，前向状态转移函数在输入值从底部进入窗口帧时把它们加入聚合状态，逆向状态转移函数则在这些值从顶部离开窗口帧时再次将其移除。因此，移除值时，总是按它们被加入时的相同顺序移除。每当调用逆向状态转移函数时，它收到的就是最早被加入但尚未被移除的参数值。逆向状态转移函数可以假定，在它移除最旧的那一行之后，当前状态中至少还会保留一行。（若非如此，窗口函数机制就会简单地重新开始一次全新的聚合，而不是使用逆向状态转移函数。）\u003c/p\u003e\u003cp\u003e用于移动聚合模式的前向状态转移函数不允许返回 NULL 作为新的状态值。如果逆向状态转移函数返回 NULL，就表示该逆向函数无法针对这个特定输入逆转状态计算，因此会从当前帧起始位置开始重新计算聚合。这一约定使得移动聚合模式可用于这样一些场景：在少数不常见的情况下，难以从当前状态值中逆向消除对应输入的影响。\u003c/p\u003e\u003cp\u003e如果没有提供移动聚合实现，聚合仍然可以与移动帧一起使用，但每当帧起点移动时，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e都会重新计算整个聚合。注意，无论聚合是否支持移动聚合模式，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 都能在不重新计算的情况下处理移动的帧结束位置；做法是继续把新值添加到聚合状态中。这就是为什么将聚合用作窗口函数时要求最终函数必须是只读的：它不能损坏聚合的状态值，这样即使已经针对一组帧边界得到了聚合结果值，聚合也能继续进行。\u003c/p\u003e\u003cp\u003e有序集聚合的语法允许对最后一个直接参数和最后一个聚合（\u003ccode class=\"literal\"\u003eWITHIN GROUP\u003c/code\u003e）参数都指定 \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e。但是，当前实现从两个方面限制了 \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e的使用。第一，有序集聚合只能使用 \u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e，不能使用其他可变参数数组类型。第二，如果最后一个直接参数是\u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e，则只能有一个聚合参数，并且它也必须是\u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e。（在系统目录使用的表示中，这两个参数会合并为单个 \u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e项，因为\u003ccode class=\"structname\"\u003epg_proc\u003c/code\u003e 无法表示具有多个\u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e参数的函数。）如果该聚合是假想集聚合，则与\u003ccode class=\"literal\"\u003eVARIADIC \"any\"\u003c/code\u003e参数匹配的直接参数就是假想参数；其前面的任何参数都表示额外的直接参数，它们不受必须与聚合参数匹配的约束。\u003c/p\u003e\u003cp\u003e当前，有序集聚合无需支持移动聚合模式，因为它们不能被用作窗口函数。\u003c/p\u003e\u003cp\u003e部分（包括并行）聚合当前不支持有序集聚合。此外，对于包含 \u003ccode class=\"literal\"\u003eDISTINCT\u003c/code\u003e或\u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e子句的聚合调用，也永远不会使用部分聚合，因为在部分聚合期间无法支持这些语义。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e见\u003ca href=\"/docs/18/xaggr.html\" title=\"36.12. 用户定义的聚合\"\u003e第 36.12 节\u003c/a\u003e。\u003c/p\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE AGGREGATE\u003c/code\u003e是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e的语言扩展。SQL 标准不提供用户定义聚合函数。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-aggregate/?v=18\" title=\"ALTER AGGREGATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER AGGREGATE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-aggregate/?v=18\" title=\"DROP AGGREGATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP AGGREGATE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE [ OR REPLACE ] AGGREGATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ( [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e [ , ... ] ) (\n    SFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e,\n    STYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\n    [ , SSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC_EXTRA ]\n    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , COMBINEFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , SERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , DESERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , INITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MINVFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSTYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC_EXTRA ]\n    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , MINITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , SORTOP = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_operator\u003c/code\u003e\u003c/em\u003e ]\n    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n)\n\nCREATE [ OR REPLACE ] AGGREGATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ( [ [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e [ , ... ] ]\n                        ORDER BY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003earg_data_type\u003c/code\u003e\u003c/em\u003e [ , ... ] ) (\n    SFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e,\n    STYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\n    [ , SSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC_EXTRA ]\n    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , INITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n    [ , HYPOTHETICAL ]\n)\n\n\u003cspan class=\"phrase\"\u003e或者旧的语法\u003c/span\u003e\n\nCREATE [ OR REPLACE ] AGGREGATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e (\n    BASETYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ebase_type\u003c/code\u003e\u003c/em\u003e,\n    SFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esfunc\u003c/code\u003e\u003c/em\u003e,\n    STYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_type\u003c/code\u003e\u003c/em\u003e\n    [ , SSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003estate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003effunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , FINALFUNC_EXTRA ]\n    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , COMBINEFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecombinefunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , SERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , DESERIALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003edeserialfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , INITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003einitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emsfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MINVFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvfunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSTYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_type\u003c/code\u003e\u003c/em\u003e ]\n    [ , MSSPACE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emstate_data_size\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003emffunc\u003c/code\u003e\u003c/em\u003e ]\n    [ , MFINALFUNC_EXTRA ]\n    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n    [ , MINITCOND = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminitial_condition\u003c/code\u003e\u003c/em\u003e ]\n    [ , SORTOP = \u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_operator\u003c/code\u003e\u003c/em\u003e ]\n)","synopsis_text":"CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n)\n\nCREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]\nORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , INITCOND = initial_condition ]\n[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]\n[ , HYPOTHETICAL ]\n)\n\n或者旧的语法\n\nCREATE [ OR REPLACE ] AGGREGATE name (\nBASETYPE = base_type,\nSFUNC = sfunc,\nSTYPE = state_data_type\n[ , SSPACE = state_data_size ]\n[ , FINALFUNC = ffunc ]\n[ , FINALFUNC_EXTRA ]\n[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , COMBINEFUNC = combinefunc ]\n[ , SERIALFUNC = serialfunc ]\n[ , DESERIALFUNC = deserialfunc ]\n[ , INITCOND = initial_condition ]\n[ , MSFUNC = msfunc ]\n[ , MINVFUNC = minvfunc ]\n[ , MSTYPE = mstate_data_type ]\n[ , MSSPACE = mstate_data_size ]\n[ , MFINALFUNC = mffunc ]\n[ , MFINALFUNC_EXTRA ]\n[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]\n[ , MINITCOND = minitial_condition ]\n[ , SORTOP = sort_operator ]\n)"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}