Provided by: postgresql-client-8.4_8.4.11-1_amd64 bug

NAME

       CREATE AGGREGATE - define a new aggregate function

SYNOPSIS

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

DESCRIPTION

       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 words:

       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.

PARAMETERS

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

       input_data_type
              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(*).)

       base_type
              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 value.

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

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

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

EXAMPLES

       See in the documentation.

COMPATIBILITY

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

SEE ALSO

       ALTER AGGREGATE [alter_aggregate(7)], DROP AGGREGATE [drop_aggregate(7)]