create_aggregate manpage

Search topic Section


       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 ]
	   [ , SORTOP = sort_operator ]

       CREATE  AGGREGATE defines a new aggregate function. Some basic and com-
       monly-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	 oper-
       ate on different input types. The name and input data type of an aggre-
       gate 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	 func-
       tion  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 begin-
       ning with the second nonnull input value.  This is handy for implement-
       ing  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.

       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	opera-
       tor.  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 opera-
       tor  for	 MIN, and > is the proper sort operator for MAX. Note that the
       optimization will never actually take effect unless the specified oper-
       ator  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.

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

       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	  [drop_aggre-

SQL - Language Statements	  2010-12-14		    CREATE AGGREGATE()