Provided by: postgresql-client-8.0_8.0.7-2build1_i386 bug


       CREATE AGGREGATE - define a new aggregate function


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


       CREATE  AGGREGATE  defines  a  new  aggregate  function. Some basic and
       commonly-used aggregate functions are included with  the  distribution;
       they  are  documented 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.
       Two aggregates in the same schema  can  have  the  same  name  if  they
       operate  on  different  input types. The name and input data type 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-item ) ---> 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 data  item,  the
       state  transition function is invoked to calculate a new internal state
       value. After all the data has 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 may provide an initial  condition,  that  is,  an
       initial  value  for  the  internal  state value.  This is specified and
       stored in the database as a column 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.  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 the  first  nonnull  input  value
       replaces  the  state  value,  and  the  transition  function is invoked
       beginning with the second nonnull  input  value.   This  is  handy  for
       implementing  aggregates  like  max.   Note  that this behavior is only
       available when state_data_type is the same  as  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 value, 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.


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

              The  input  data type on which this aggregate function operates.
              This can be specified as "ANY" for an aggregate  that  does  not
              examine its input values (an example is count(*)).

       sfunc  The  name of the state transition function to be called for each
              input data value. This is normally a function of two  arguments,
              the  first  being of type state_data_type and the second of type
              input_data_type. Alternatively, for an aggregate that  does  not
              examine  its  input values, the function takes just one argument
              of type state_data_type. In either case the function must return
              a value of type state_data_type. This function takes the current
              state value and the current input data  item,  and  returns  the
              next state value.

              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 data has  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  parameters  of  CREATE  AGGREGATE can be written in any order, not
       just the order illustrated above.


       See 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