Home • Explore • Blog • CI
☰



postgresql
12th chunk of `doc/src/sgml/ref/create_aggregate.sgml`
e6becd05b1ccd7affeaf7f7734e94b7d87888a65938fd6b40000000100000869
 them again when they leave the frame at the top.  So,
    when values are removed, they are always removed in the same order they
    were added.  Whenever the inverse transition function is invoked, it will
    thus receive the earliest added but not yet removed argument value(s).
    The inverse transition function can assume that at least one row will
    remain in the current state after it removes the oldest row.  (When this
    would not be the case, the window function mechanism simply starts a
    fresh aggregation, rather than using the inverse transition function.)
   </para>

   <para>
    The forward transition function for moving-aggregate mode is not
    allowed to return NULL as the new state value. If the inverse
    transition function returns NULL, this is taken as an indication that
    the inverse function cannot reverse the state calculation for this
    particular input, and so the aggregate calculation will be redone from
    scratch for the current frame starting position.  This convention
    allows moving-aggregate mode to be used in situations where there are
    some infrequent cases that are impractical to reverse out of the
    running state value.
   </para>

   <para>
    If no moving-aggregate implementation is supplied,
    the aggregate can still be used with moving frames,
    but <productname>PostgreSQL</productname> will recompute the whole
    aggregation whenever the start of the frame moves.
    Note that whether or not the aggregate supports moving-aggregate
    mode, <productname>PostgreSQL</productname> can handle a moving frame
    end without recalculation; this is done by continuing to add new values
    to the aggregate's state.  This is why use of an aggregate as a window
    function requires that the final function be read-only: it must
    not damage the aggregate's state value, so that the aggregation can be
    continued even after an aggregate result value has been obtained for
    one set of frame boundaries.
   </para>

   <para>
    The syntax for ordered-set aggregates allows <literal>VARIADIC</literal>
    to be specified for both the last direct parameter
Title: Moving-Aggregate Mode and Window Functions: Forward/Inverse Transition Functions and Recalculation
Summary
This section further elaborates on moving-aggregate mode in window functions, detailing how forward and inverse transition functions manage state when rows enter and leave the window frame. It highlights the limitations on NULL returns, the consequences when no moving-aggregate implementation is provided (leading to full recalculation), and how PostgreSQL handles moving frame ends by continuing to add values to the aggregate state. The need for a read-only final function is emphasized to preserve the aggregate's state for continued aggregation.