Index for Section AGGREGATE

Alphabetical listing for C

Bottom of page

CREATE
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 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 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(5)], DROP AGGREGATE [drop_aggregate(l)]

Index for Section AGGREGATE

Alphabetical listing for C

Top of page