<commit_before><?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="bm20941">CREATE AGGREGATE</title><body><p id="sql_command_desc">Defines a new aggregate function.</p><section id="section2"
      ><title>Synopsis</title><varname>argname</varname><codeblock id="sql_command_synopsis">CREATE AGGREGATE <varname>name</varname> ( [ <varname>argmode</varname> ] [  ] <varname>arg_data_type</varname> [ , ... ] ) (
    SFUNC = <varname>sfunc</varname>,
    STYPE = <varname>state_data_type</varname>
    [ , SSPACE = <varname>state_data_size</varname> ]
    [ , FINALFUNC = <varname>ffunc</varname> ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = <varname>combinefunc</varname> ]
    [ , SERIALFUNC = <varname>serialfunc</varname> ]
    [ , DESERIALFUNC = <varname>deserialfunc</varname> ]
    [ , INITCOND = <varname>initial_condition</varname> ]
    [ , MSFUNC = <varname>msfunc</varname> ]
    [ , MINVFUNC = <varname>minvfunc</varname> ]
    [ , MSTYPE = <varname>mstate_data_type</varname> ]
    [ , MSSPACE = <varname>mstate_data_size</varname> ]
    [ , MFINALFUNC = <varname>mffunc</varname> ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = <varname>minitial_condition</varname> ]
    [ , SORTOP = <varname>sort_operator</varname> ]
  )
  
  CREATE AGGREGATE <varname>name</varname> ( [ [ <varname>argmode</varname> ] [ <varname>argname</varname> ] <varname>arg_data_type</varname> [ , ... ] ]
      ORDER BY [ <varname>argmode</varname> ] [ <varname>argname</varname> ] <varname>arg_data_type</varname> [ , ... ] ) (
    SFUNC = <varname>sfunc</varname>,
    STYPE = <varname>state_data_type</varname>
    [ , SSPACE = <varname>state_data_size</varname> ]
    [ , FINALFUNC = <varname>ffunc</varname> ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = <varname>combinefunc</varname> ]
    [ , SERIALFUNC = <varname>serialfunc</varname> ]
    [ , DESERIALFUNC = <varname>deserialfunc</varname> ]
    [ , INITCOND = <varname>initial_condition</varname> ]
    [ , HYPOTHETICAL ]
  )
  
  or the old syntax
  
  CREATE AGGREGATE <varname>name</varname> (
    BASETYPE = <varname>base_type</varname>,
    SFUNC = <varname>sfunc</varname>,
    STYPE = <varname>state_data_type</varname>
    [ , SSPACE = <varname>state_data_size</varname> ]
    [ , FINALFUNC = <varname>ffunc</varname> ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = <varname>combinefunc</varname> ]
    [ , SERIALFUNC = <varname>serialfunc</varname> ]
    [ , DESERIALFUNC = <varname>deserialfunc</varname> ]
    [ , INITCOND = <varname>initial_condition</varname> ]
    [ , MSFUNC = <varname>msfunc</varname> ]
    [ , MINVFUNC = <varname>minvfunc</varname> ]
    [ , MSTYPE = <varname>mstate_data_type</varname> ]
    [ , MSSPACE = <varname>mstate_data_size</varname> ]
    [ , MFINALFUNC = <varname>mffunc</varname> ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = <varname>minitial_condition</varname> ]
    [ , SORTOP = <varname>sort_operator</varname> ]
  )</codeblock></section><section id="section3"><title>Description</title><p><codeph>CREATE AGGREGATE</codeph> defines a new
        aggregate function. Some basic and commonly-used aggregate functions such as
          <codeph>count</codeph>, <codeph>min</codeph>, <codeph>max</codeph>, <codeph>sum</codeph>,
          <codeph>avg</codeph> and so on are already provided in Greenplum Database. If one defines
        new types or needs an aggregate function not already provided, then <codeph>CREATE
          AGGREGATE</codeph> can be used to provide the desired features.</p>
      <p>If a schema name is given (for example, <codeph>CREATE AGGREGATE myschema.myagg
          ...</codeph>) then the aggregate function is created in the specified schema. Otherwise it
        is created in the current schema. </p>
      <p>An aggregate function is identified by its name and input data types. Two aggregate
        functions in the same schema can have the same name if they operate on different input
        types. The name and input data types of an aggregate function must also be distinct from the
        name and input data types of every ordinary function in the same schema. This behavior is
        identical to overloading of ordinary function names. See <codeph><xref
            href="CREATE_FUNCTION.xml#topic1"/></codeph>.</p>

      <p>A simple aggregate function is made from one, two, or three ordinary functions (which must
        be <codeph>IMMUTABLE</codeph> functions): </p>
      <ul id="ul_d5c_5yl_dhb">
        <li>a state transition function <varname>sfunc</varname></li>
        <li>an optional final calculation function <varname>ffunc</varname></li>
        <li>an optional combine function <varname>combinefunc</varname></li>
      </ul>
   
	<p>These functions are used as
        follows:</p><codeblock><varname>sfunc</varname>( internal-state, next-data-values ) ---&gt; next-internal-state
<varname>ffunc</varname>( internal-state ) ---&gt; aggregate-value
<varname>combinefunc</varname>( internal-state, internal-state ) ---&gt; next-internal-state</codeblock>
      <p>Greenplum Database creates a temporary variable of data type <varname>stype</varname> to
        hold the current internal state of the aggregate function. At each input row, the aggregate
        argument values are calculated and the state transition function is invoked with the current
        state value and the new argument values to calculate a new internal state value. After all
        the rows have been processed, the final function is invoked once to calculate the aggregate
        return value. If there is no final function then the ending state value is returned
        as-is.</p><p>You can specify <codeph><varname>combinefunc</varname></codeph> as a method for optimizing
        aggregate execution. By specifying <codeph><varname>combinefunc</varname></codeph>, the
        aggregate can be executed in parallel on segments first and then on the master. When a
        two-level execution is performed, <codeph><varname>sfunc</varname></codeph> is executed on
        the segments to generate partial aggregate results, and
            <codeph><varname>combinefunc</varname></codeph> is executed on the master to aggregate
        the partial results from segments. If single-level aggregation is performed, all the rows
        are sent to the master and <codeph><varname>sfunc</varname></codeph> is applied to the
        rows.</p><p>Single-level aggregation and two-level aggregation are equivalent execution strategies. Either
        type of aggregation can be implemented in a query plan. When you implement the functions
          <codeph>combinefunc</codeph> and <codeph>sfunc</codeph>, you must ensure that the
        invocation of <codeph>sfunc</codeph> on the segment instances followed by
          <codeph>combinefunc</codeph> on the master produce the same result as single-level
        aggregation that sends all the rows to the master and then applies only the
          <codeph>sfunc</codeph> to the rows.</p><p>An aggregate function can
        provide an optional initial condition, an initial value for the internal state value. This
        is specified and stored in the database as a value of type text, 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 <codeph>NULL</codeph>. </p><p>If the state transition
        function is declared <codeph>STRICT</codeph>, then it cannot be called with
          <codeph>NULL</codeph> 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 <codeph>NULL</codeph>, then
        at the first row with all non-null input values, the first argument value replaces the state
        value, and the transition function is invoked at subsequent rows with all non-null input
        values. This is useful for implementing aggregates like <codeph>max</codeph>. Note that this
        behavior is only available when <varname>state_data_type</varname> is the same as the first
          <varname>input_data_type</varname>. When these types are different, you must supply a non-null initial
        condition or use a nonstrict transition function.</p><p>If the state transition function is not declared <codeph>STRICT</codeph>, then it will be called
        unconditionally at each input row, and must deal with <codeph>NULL</codeph> inputs and
          <codeph>NULL</codeph> transition values for itself. This allows the aggregate author to
        have full control over the aggregate's handling of <codeph>NULL</codeph> values.</p><p>If the final function is declared
          <codeph>STRICT</codeph>, then it will not be called when the ending state value is
          <codeph>NULL</codeph>; instead a <codeph>NULL</codeph> result will be returned
        automatically. (This is the normal behavior of <codeph>STRICT</codeph> functions.) In any
        case the final function has the option of returning a <codeph>NULL</codeph> value. For
        example, the final function for <codeph>avg</codeph> returns <codeph>NULL</codeph> when it
        sees there were zero input rows.</p>
    <p>
      Sometimes 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
      <codeph>NULL</codeph> (and so the final function must not be strict when
      the <codeph>FINALFUNC_EXTRA</codeph> option is used), but nonetheless they
        are valid parameters.  The final function could for example make use
        of <codeph>get_fn_expr_argtype</codeph> to identify the actual argument type
          in the current call.
    </p>
      <p> An aggregate can optionally support <i>moving-aggregate mode</i>, as described in <xref
          href="https://www.postgresql.org/docs/9.4/xaggr.html#XAGGR-MOVING-AGGREGATES"
          scope="external" format="html">Moving-Aggregate Mode</xref> in the PostgreSQL
        documentation. This requires specifying the <codeph><varname>msfunc</varname></codeph>,
            <codeph><varname>minfunc</varname></codeph>, and
          <codeph><varname>mstype</varname></codeph> functions, and optionally the
            <codeph><varname>mspace</varname></codeph>,
          <codeph><varname>mfinalfunc</varname></codeph>,
            <codeph><varname>mfinalfunc_extra</varname></codeph>, and
            <codeph><varname>minitcond</varname></codeph> functions. Except for
            <codeph><varname>minvfunc</varname></codeph>, these functions work like the
        corresponding simple-aggregate functions without <codeph><varname>m</varname></codeph>; they
        define a separate implementation of the aggregate that includes an inverse transition
        function. </p>   
      <p>The syntax with <codeph>ORDER BY</codeph> in the parameter list creates a special type of
        aggregate called an <i>ordered-set aggregate</i>; or if <codeph>HYPOTHETICAL</codeph> is
        specified, then a <i>hypothetical-set aggregate</i> 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 <i>direct</i> 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 "hypothetical" row. </p>
    <p>Single argument aggregate functions, such as min or
        max, 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 sort operator. The
        basic requirement is that the aggregate must yield the first element in the sort ordering
        induced by the operator; in other
        words:</p><codeblock>SELECT <varname>agg</varname>(<varname>col</varname>) FROM <varname>tab</varname>; </codeblock>
      <p>must be equivalent to:</p>
    <codeblock>SELECT <varname>col</varname> FROM <varname>tab</varname> ORDER BY <varname>col</varname> USING <varname>sortop</varname> LIMIT 1;</codeblock><p>Further
        assumptions are that the aggregate function ignores <codeph>NULL</codeph> inputs, and that
        it delivers a <codeph>NULL</codeph> result if and only if there were no non-null inputs.
        Ordinarily, a data type's <codeph>&lt;</codeph> operator is the proper sort operator for
          <codeph>MIN</codeph>, and <codeph>&gt;</codeph> is the proper sort operator for
          <codeph>MAX</codeph>. Note that the optimization will never actually take effect unless
        the specified operator is the "less than" or "greater than" strategy member of a B-tree
        index operator class.</p>
      <p> To be able to create an aggregate function, you must have <codeph>USAGE</codeph> privilege
        on the argument types, the state type, and the return type, as well as
          <codeph>EXECUTE</codeph> privilege on the transition and final functions. </p>
</section><section id="section5"><title>Parameters</title>
      <parml><commit_msg>docs - advice for preventing OOM with C UDAs (#8470)<commit_after><?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="bm20941">CREATE AGGREGATE</title><body><p id="sql_command_desc">Defines a new aggregate function.</p><section id="section2"
      ><title>Synopsis</title><varname>argname</varname><codeblock id="sql_command_synopsis">CREATE AGGREGATE <varname>name</varname> ( [ <varname>argmode</varname> ] [  ] <varname>arg_data_type</varname> [ , ... ] ) (
    SFUNC = <varname>sfunc</varname>,
    STYPE = <varname>state_data_type</varname>
    [ , SSPACE = <varname>state_data_size</varname> ]
    [ , FINALFUNC = <varname>ffunc</varname> ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = <varname>combinefunc</varname> ]
    [ , SERIALFUNC = <varname>serialfunc</varname> ]
    [ , DESERIALFUNC = <varname>deserialfunc</varname> ]
    [ , INITCOND = <varname>initial_condition</varname> ]
    [ , MSFUNC = <varname>msfunc</varname> ]
    [ , MINVFUNC = <varname>minvfunc</varname> ]
    [ , MSTYPE = <varname>mstate_data_type</varname> ]
    [ , MSSPACE = <varname>mstate_data_size</varname> ]
    [ , MFINALFUNC = <varname>mffunc</varname> ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = <varname>minitial_condition</varname> ]
    [ , SORTOP = <varname>sort_operator</varname> ]
  )
  
  CREATE AGGREGATE <varname>name</varname> ( [ [ <varname>argmode</varname> ] [ <varname>argname</varname> ] <varname>arg_data_type</varname> [ , ... ] ]
      ORDER BY [ <varname>argmode</varname> ] [ <varname>argname</varname> ] <varname>arg_data_type</varname> [ , ... ] ) (
    SFUNC = <varname>sfunc</varname>,
    STYPE = <varname>state_data_type</varname>
    [ , SSPACE = <varname>state_data_size</varname> ]
    [ , FINALFUNC = <varname>ffunc</varname> ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = <varname>combinefunc</varname> ]
    [ , SERIALFUNC = <varname>serialfunc</varname> ]
    [ , DESERIALFUNC = <varname>deserialfunc</varname> ]
    [ , INITCOND = <varname>initial_condition</varname> ]
    [ , HYPOTHETICAL ]
  )
  
  or the old syntax
  
  CREATE AGGREGATE <varname>name</varname> (
    BASETYPE = <varname>base_type</varname>,
    SFUNC = <varname>sfunc</varname>,
    STYPE = <varname>state_data_type</varname>
    [ , SSPACE = <varname>state_data_size</varname> ]
    [ , FINALFUNC = <varname>ffunc</varname> ]
    [ , FINALFUNC_EXTRA ]
    [ , COMBINEFUNC = <varname>combinefunc</varname> ]
    [ , SERIALFUNC = <varname>serialfunc</varname> ]
    [ , DESERIALFUNC = <varname>deserialfunc</varname> ]
    [ , INITCOND = <varname>initial_condition</varname> ]
    [ , MSFUNC = <varname>msfunc</varname> ]
    [ , MINVFUNC = <varname>minvfunc</varname> ]
    [ , MSTYPE = <varname>mstate_data_type</varname> ]
    [ , MSSPACE = <varname>mstate_data_size</varname> ]
    [ , MFINALFUNC = <varname>mffunc</varname> ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = <varname>minitial_condition</varname> ]
    [ , SORTOP = <varname>sort_operator</varname> ]
  )</codeblock></section><section id="section3">
      <title>Description</title>
      <p><codeph>CREATE AGGREGATE</codeph> defines a new aggregate function. Some basic and
        commonly-used aggregate functions such as <codeph>count</codeph>, <codeph>min</codeph>,
          <codeph>max</codeph>, <codeph>sum</codeph>, <codeph>avg</codeph> and so on are already
        provided in Greenplum Database. If you define new types or need an aggregate function not
        already provided, you can use <codeph>CREATE AGGREGATE</codeph> to provide the desired
        features.</p>
      <p>If a schema name is given (for example, <codeph>CREATE AGGREGATE myschema.myagg
          ...</codeph>) then the aggregate function is created in the specified schema. Otherwise it
        is created in the current schema. </p>
      <p>An aggregate function is identified by its name and input data types. Two aggregate
        functions in the same schema can have the same name if they operate on different input
        types. The name and input data types of an aggregate function must also be distinct from the
        name and input data types of every ordinary function in the same schema. This behavior is
        identical to overloading of ordinary function names. See <codeph><xref
            href="CREATE_FUNCTION.xml#topic1"/></codeph>.</p>
      <p>A simple aggregate function is made from one, two, or three ordinary functions (which must
        be <codeph>IMMUTABLE</codeph> functions): </p>
      <ul id="ul_d5c_5yl_dhb">
        <li>a state transition function <varname>sfunc</varname></li>
        <li>an optional final calculation function <varname>ffunc</varname></li>
        <li>an optional combine function <varname>combinefunc</varname></li>
      </ul>
       <p>These functions are used as
        follows:</p><codeblock><varname>sfunc</varname>( internal-state, next-data-values ) ---&gt; next-internal-state
<varname>ffunc</varname>( internal-state ) ---&gt; aggregate-value
<varname>combinefunc</varname>( internal-state, internal-state ) ---&gt; next-internal-state</codeblock>
      <p>Greenplum Database creates a temporary variable of data type <varname>stype</varname> to
        hold the current internal state of the aggregate function. At each input row, the aggregate
        argument values are calculated and the state transition function is invoked with the current
        state value and the new argument values to calculate a new internal state value. After all
        the rows have been processed, the final function is invoked once to calculate the aggregate
        return value. If there is no final function then the ending state value is returned
        as-is.</p>
      <note>If you write a user-defined aggregate in C, and you declare the state value
          (<varname>stype</varname>) as type <codeph>internal</codeph>, there is a risk of an
        out-of-memory error occurring. If <codeph>internal</codeph> state values are not properly
        managed and a query acquires too much memory for state values, an out-of-memory error could
        occur. To prevent this, use <codeph>mpool_alloc(<varname>mpool</varname>,
            <varname>size</varname>)</codeph> to have Greenplum manage and allocate memory for
        non-temporary state values, that is, state values that have a lifespan for the entire
        aggregation. The argument <codeph><varname>mpool</varname></codeph> of the
          <codeph>mpool_alloc()</codeph> function is
          <codeph>aggstate->hhashtable->group_buf</codeph>. For an example, see the implementation
        of the numeric data type aggregates in <codeph>src/backend/utils/adt/numeric.c</codeph> in
        the Greenplum Database open source code.</note>
      <p>You can specify <codeph><varname>combinefunc</varname></codeph> as a method for optimizing
        aggregate execution. By specifying <codeph><varname>combinefunc</varname></codeph>, the
        aggregate can be executed in parallel on segments first and then on the master. When a
        two-level execution is performed, <codeph><varname>sfunc</varname></codeph> is executed on
        the segments to generate partial aggregate results, and
            <codeph><varname>combinefunc</varname></codeph> is executed on the master to aggregate
        the partial results from segments. If single-level aggregation is performed, all the rows
        are sent to the master and <codeph><varname>sfunc</varname></codeph> is applied to the
        rows.</p><p>Single-level aggregation and two-level aggregation are equivalent execution strategies. Either
        type of aggregation can be implemented in a query plan. When you implement the functions
          <codeph>combinefunc</codeph> and <codeph>sfunc</codeph>, you must ensure that the
        invocation of <codeph>sfunc</codeph> on the segment instances followed by
          <codeph>combinefunc</codeph> on the master produce the same result as single-level
        aggregation that sends all the rows to the master and then applies only the
          <codeph>sfunc</codeph> to the rows.</p><p>An aggregate function can
        provide an optional initial condition, an initial value for the internal state value. This
        is specified and stored in the database as a value of type text, 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 <codeph>NULL</codeph>. </p><p>If the state transition
        function is declared <codeph>STRICT</codeph>, then it cannot be called with
          <codeph>NULL</codeph> 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 <codeph>NULL</codeph>, then
        at the first row with all non-null input values, the first argument value replaces the state
        value, and the transition function is invoked at subsequent rows with all non-null input
        values. This is useful for implementing aggregates like <codeph>max</codeph>. Note that this
        behavior is only available when <varname>state_data_type</varname> is the same as the first
          <varname>input_data_type</varname>. When these types are different, you must supply a non-null initial
        condition or use a nonstrict transition function.</p><p>If the state transition function is not declared <codeph>STRICT</codeph>, then it will be called
        unconditionally at each input row, and must deal with <codeph>NULL</codeph> inputs and
          <codeph>NULL</codeph> transition values for itself. This allows the aggregate author to
        have full control over the aggregate's handling of <codeph>NULL</codeph> values.</p><p>If the final function is declared
          <codeph>STRICT</codeph>, then it will not be called when the ending state value is
          <codeph>NULL</codeph>; instead a <codeph>NULL</codeph> result will be returned
        automatically. (This is the normal behavior of <codeph>STRICT</codeph> functions.) In any
        case the final function has the option of returning a <codeph>NULL</codeph> value. For
        example, the final function for <codeph>avg</codeph> returns <codeph>NULL</codeph> when it
        sees there were zero input rows.</p>
    <p>
      Sometimes 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
      <codeph>NULL</codeph> (and so the final function must not be strict when
      the <codeph>FINALFUNC_EXTRA</codeph> option is used), but nonetheless they
        are valid parameters.  The final function could for example make use
        of <codeph>get_fn_expr_argtype</codeph> to identify the actual argument type
          in the current call.
    </p>
      <p> An aggregate can optionally support <i>moving-aggregate mode</i>, as described in <xref
          href="https://www.postgresql.org/docs/9.4/xaggr.html#XAGGR-MOVING-AGGREGATES"
          scope="external" format="html">Moving-Aggregate Mode</xref> in the PostgreSQL
        documentation. This requires specifying the <codeph><varname>msfunc</varname></codeph>,
            <codeph><varname>minfunc</varname></codeph>, and
          <codeph><varname>mstype</varname></codeph> functions, and optionally the
            <codeph><varname>mspace</varname></codeph>,
          <codeph><varname>mfinalfunc</varname></codeph>,
            <codeph><varname>mfinalfunc_extra</varname></codeph>, and
            <codeph><varname>minitcond</varname></codeph> functions. Except for
            <codeph><varname>minvfunc</varname></codeph>, these functions work like the
        corresponding simple-aggregate functions without <codeph><varname>m</varname></codeph>; they
        define a separate implementation of the aggregate that includes an inverse transition
        function. </p>
      <p>The syntax with <codeph>ORDER BY</codeph> in the parameter list creates a special type of
        aggregate called an <i>ordered-set aggregate</i>; or if <codeph>HYPOTHETICAL</codeph> is
        specified, then a <i>hypothetical-set aggregate</i> 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 <i>direct</i> 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 "hypothetical" row. </p>
    <p>Single argument aggregate functions, such as min or
        max, 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 sort operator. The
        basic requirement is that the aggregate must yield the first element in the sort ordering
        induced by the operator; in other
        words:</p><codeblock>SELECT <varname>agg</varname>(<varname>col</varname>) FROM <varname>tab</varname>; </codeblock>
      <p>must be equivalent to:</p>
    <codeblock>SELECT <varname>col</varname> FROM <varname>tab</varname> ORDER BY <varname>col</varname> USING <varname>sortop</varname> LIMIT 1;</codeblock><p>Further
        assumptions are that the aggregate function ignores <codeph>NULL</codeph> inputs, and that
        it delivers a <codeph>NULL</codeph> result if and only if there were no non-null inputs.
        Ordinarily, a data type's <codeph>&lt;</codeph> operator is the proper sort operator for
          <codeph>MIN</codeph>, and <codeph>&gt;</codeph> is the proper sort operator for
          <codeph>MAX</codeph>. Note that the optimization will never actually take effect unless
        the specified operator is the "less than" or "greater than" strategy member of a B-tree
        index operator class.</p>
      <p> To be able to create an aggregate function, you must have <codeph>USAGE</codeph> privilege
        on the argument types, the state type, and the return type, as well as
          <codeph>EXECUTE</codeph> privilege on the transition and final functions. </p>
</section><section id="section5"><title>Parameters</title>
      <parml>
{'fraction_non_alphanumeric': 0.09939696402578499, 'fraction_numerical': 0.0013862895958965827, 'mean_word_length': 3.4847684177805407, 'pattern_counts': {'":': 0, '<': 753, '<?xml version=': 2, '>': 755, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 4}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7056523', 'n_tokens_mistral': 8244, 'n_tokens_neox': 8110, 'n_words': 2860}