Man Linux: Main Page and Category List


       CREATE AGGREGATE - define a new aggregate function


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

       or the old syntax

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


       CREATE  AGGREGATE  defines  a  new  aggregate  function. Some basic and
       commonly-used aggregate functions are included with  the  distribution;
       they  are  documented in in the documentation. If one defines new types
       or needs an  aggregate  function  not  already  provided,  then  CREATE
       AGGREGATE can be used to provide the desired features.

       If a schema name is given (for example, CREATE AGGREGATE myschema.myagg
       ...) then the aggregate function is created in  the  specified  schema.
       Otherwise it is created in the current schema.

       An 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.

       An  aggregate  function  is  made from one or two ordinary functions: a
       state transition function sfunc,  and  an  optional  final  calculation
       function ffunc.  These are used as follows:

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

       PostgreSQL  creates a temporary variable of data type stype 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.

       An 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 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 null.

       If the state transition function is declared ‘‘strict’’, 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 subsequent  rows
       with   all-nonnull  input  values.   This  is  handy  for  implementing
       aggregates like max.  Note that this behavior is  only  available  when
       state_data_type  is  the same as the first input_data_type.  When these
       types are different, you must supply a nonnull initial condition or use
       a nonstrict transition function.

       If  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  transition values for itself. This allows the aggregate author to
       have full control over the aggregate’s handling of null values.

       If the final function is declared  ‘‘strict’’,  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 avg returns
       null when it sees there were zero input rows.

       Aggregates  that  behave  like 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

       SELECT agg(col) FROM tab;

       must be equivalent to:

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

       Further 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  <  operator  is  the  proper  sort
       operator  for MIN, and > is the proper sort operator for MAX. 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.


       name   The name (optionally schema-qualified) of the aggregate function
              to create.

              An  input  data  type on which this aggregate function operates.
              To create a zero-argument aggregate function, write *  in  place
              of  the  list  of  input  data  types.  (An  example  of such an
              aggregate is count(*).)

              In the old syntax for CREATE AGGREGATE, the input data  type  is
              specified by a basetype 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,
              specify the basetype as "ANY" (not *).

       sfunc  The name of the state transition function to be called for  each
              input  row. For an N-argument aggregate function, the sfunc must
              take N+1 arguments, the first being of type state_data_type  and
              the  rest  matching  the  declared  input  data  type(s)  of the
              aggregate.   The  function  must  return   a   value   of   type
              state_data_type. This function takes the current state value and
              the current input data value(s),  and  returns  the  next  state

              The data type for the aggregate’s state value.

       ffunc  The name of the final function called to compute the aggregate’s
              result after all input rows have been  traversed.  The  function
              must  take a single argument of type state_data_type. The return
              data type of the aggregate is defined as the return type of this
              function. If ffunc is not specified, then the ending state value
              is used as the  aggregate’s  result,  and  the  return  type  is

              The  initial  setting for the state value. This must be a string
              constant in the form accepted for the data type state_data_type.
              If not specified, the state value starts out null.

              The  associated  sort operator for a MIN- or MAX-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 aggregate).

       The parameters of CREATE AGGREGATE can be written  in  any  order,  not
       just the order illustrated above.


       See in the documentation.


       CREATE  AGGREGATE  is a PostgreSQL language extension. The SQL standard
       does not provide for user-defined aggregate functions.


       ALTER     AGGREGATE      [alter_aggregate(7)],      DROP      AGGREGATE