Unix/Linux Go Back    

RedHat 9 (Linux i386) - man page for create_aggregate (redhat section 7)

Linux & Unix Commands - Search Man Pages
Man Page or Keyword Search:   man
Select Man Page Set:       apropos Keyword Search (sections above)


       CREATE AGGREGATE - define a new aggregate function

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

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

	      The  input data type on which this aggregate function operates.  This can be speci-
	      fied 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  function  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.

	      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.

	      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.

	      Message returned if the command completes successfully.

       CREATE  AGGREGATE allows a user or programmer to extend PostgreSQL functionality by defin-
       ing new aggregate functions. Some aggregate functions for base types such as  min(integer)
       and  avg(double	precision)  are already provided 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 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 cur-
       rent schema (the one at the front of the search path; see 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 func-
       tion 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 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'', 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 condition or use a non-strict
       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 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.

       Use DROP AGGREGATE to drop aggregate functions.

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

       Refer to the chapter on aggregate functions in the PostgreSQL Programmer's Guide for  com-
       plete examples of usage.

       CREATE  AGGREGATE  is  a  PostgreSQL  language extension.  There is no CREATE AGGREGATE in

SQL - Language Statements		    2002-11-22			      CREATE AGGREGATE(7)
Unix & Linux Commands & Man Pages : ©2000 - 2018 Unix and Linux Forums

All times are GMT -4. The time now is 02:41 PM.