CREATE AGGREGATE - Defines a new aggregate function


SYNOPSIS

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

   INPUTS
       name   The name of an aggregate function to create.

       input_data_type
              The  input  data type on which this aggregate func­
              tion 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_type   and   the   second   of   type
              input_data_type.  Alternatively, for  an  aggregate
              that  does  not examine its input values, the func­
              tion takes just one argument  of  type  state_type.
              In  either case the function must return a value of
              type state_type.  This function takes  the  current
              state  value  and  the current input data item, and
              returns the next state value.

       state_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 data has
              been traversed. The function  must  take  a  single
              argument  of type state_type.  The output 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 output type is state_type.

       initial_condition
              The  initial setting for the state value. This must
              be a literal constant in the form accepted for  the
              data  type state_type.  If not specified, the state
              value starts out NULL.

   OUTPUTS
       CREATE Message returned if the command completes  success­
              fully.

       CREATE  AGGREGATE  allows  a  user or programmer to extend
       Postgres functionality by  defining  new  aggregate  func­
       tions.  Some  aggregate  functions  for base types such as
       min(integer) and avg(double precision)  are  already  pro­
       vided  in  the base distribution. If one defines new types
       or needs an aggregate function not already provided,  then
       CREATE  AGGREGATE  can be used to provide the desired fea­
       tures.

       An aggregate function is identified by its name and  input
       data  type.  Two aggregates can have the same name if they
       operate on different input types. To avoid  confusion,  do
       not  make  an ordinary function of the same name and input
       data type as an aggregate.

       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

       Postgres  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 output 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 field 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" in
       pg_proc, 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  non-NULL
       input  value  replaces the state value, and the transition
       function is invoked beginning  with  the  second  non-NULL
       input  value.   This  is handy for implementing aggregates
       like max.  Note that this behavior is only available  when
       state_type  is  the  same  as input_data_type.  When these
       types are different, you must supply  a  non-NULL  initial


       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 con­
       trol over the aggregate's handling of NULLs.

       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 output automatically. (Of course
       this is just the normal behavior of strict functions.)  In
       any  case  the  final function has the option of returning
       NULL. For example, the final function for avg returns NULL
       when it sees there were zero input tuples.

   NOTES
       Use DROP AGGREGATE to drop aggregate functions.

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


USAGE

       Refer to the chapter on aggregate functions in  the  Post­
       greSQL  Programmer's Guide for complete examples of usage.


COMPATIBILITY

   SQL92
       CREATE AGGREGATE is a Postgres language extension.   There
       is no CREATE AGGREGATE in SQL92.


Man(1) output converted with man2html