<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>User-Defined Aggregates</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Extending SQL"
HREF="extend.html"><LINK
REL="PREVIOUS"
TITLE="C-Language Functions"
HREF="xfunc-c.html"><LINK
REL="NEXT"
TITLE="User-Defined Types"
HREF="xtypes.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="xfunc-c.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="extend.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 31. Extending <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="extend.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="xtypes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="XAGGR"
>31.10. User-Defined Aggregates</A
></H1
><A
NAME="AEN30705"
></A
><P
> Aggregate functions in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
are expressed as <I
CLASS="FIRSTTERM"
>state values</I
>
and <I
CLASS="FIRSTTERM"
>state transition functions</I
>.
That is, an aggregate can be
defined in terms of state that is modified whenever an
input item is processed. To define a new aggregate
function, one selects a data type for the state value,
an initial value for the state, and a state transition
function. The state transition function is just an
ordinary function that could also be used outside the
context of the aggregate. A <I
CLASS="FIRSTTERM"
>final function</I
>
can also be specified, in case the desired result of the aggregate
is different from the data that needs to be kept in the running
state value.
</P
><P
> Thus, in addition to the argument and result data types seen by a user
of the aggregate, there is an internal state-value data type that
may be different from both the argument and result types.
</P
><P
> If we define an aggregate that does not use a final function,
we have an aggregate that computes a running function of
the column values from each row. <CODE
CLASS="FUNCTION"
>sum</CODE
> is an
example of this kind of aggregate. <CODE
CLASS="FUNCTION"
>sum</CODE
> starts at
zero and always adds the current row's value to
its running total. For example, if we want to make a <CODE
CLASS="FUNCTION"
>sum</CODE
>
aggregate to work on a data type for complex numbers,
we only need the addition function for that data type.
The aggregate definition would be:
</P><PRE
CLASS="SCREEN"
>CREATE AGGREGATE complex_sum (
sfunc = complex_add,
basetype = complex,
stype = complex,
initcond = '(0,0)'
);
SELECT complex_sum(a) FROM test_complex;
complex_sum
-------------
(34,53.9)</PRE
><P>
(In practice, we'd just name the aggregate <CODE
CLASS="FUNCTION"
>sum</CODE
> and rely on
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> to figure out which kind
of sum to apply to a column of type <TT
CLASS="TYPE"
>complex</TT
>.)
</P
><P
> The above definition of <CODE
CLASS="FUNCTION"
>sum</CODE
> will return zero (the initial
state condition) if there are no nonnull input values.
Perhaps we want to return null in that case instead — the SQL standard
expects <CODE
CLASS="FUNCTION"
>sum</CODE
> to behave that way. We can do this simply by
omitting the <TT
CLASS="LITERAL"
>initcond</TT
> phrase, so that the initial state
condition is null. Ordinarily this would mean that the <TT
CLASS="LITERAL"
>sfunc</TT
>
would need to check for a null state-condition input, but for
<CODE
CLASS="FUNCTION"
>sum</CODE
> and some other simple aggregates like
<CODE
CLASS="FUNCTION"
>max</CODE
> and <CODE
CLASS="FUNCTION"
>min</CODE
>,
it is sufficient to insert the first nonnull input value into
the state variable and then start applying the transition function
at the second nonnull input value. <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
will do that automatically if the initial condition is null and
the transition function is marked <SPAN
CLASS="QUOTE"
>"strict"</SPAN
> (i.e., not to be called
for null inputs).
</P
><P
> Another bit of default behavior for a <SPAN
CLASS="QUOTE"
>"strict"</SPAN
> transition function
is that the previous state value is retained unchanged whenever a
null input value is encountered. Thus, null values are ignored. If you
need some other behavior for null inputs, just do not define your transition
function as strict, and code it to test for null inputs and do
whatever is needed.
</P
><P
> <CODE
CLASS="FUNCTION"
>avg</CODE
> (average) is a more complex example of an aggregate. It requires
two pieces of running state: the sum of the inputs and the count
of the number of inputs. The final result is obtained by dividing
these quantities. Average is typically implemented by using a
two-element array as the state value. For example,
the built-in implementation of <CODE
CLASS="FUNCTION"
>avg(float8)</CODE
>
looks like:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE AGGREGATE avg (
sfunc = float8_accum,
basetype = float8,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0}'
);</PRE
><P>
</P
><P
> Aggregate functions may use polymorphic
state transition functions or final functions, so that the same functions
can be used to implement multiple aggregates.
See <A
HREF="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC"
>Section 31.2.5</A
>
for an explanation of polymorphic functions.
Going a step further, the aggregate function itself may be specified
with a polymorphic base type and state type, allowing a single
aggregate definition to serve for multiple input data types.
Here is an example of a polymorphic aggregate:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);</PRE
><P>
Here, the actual state type for any aggregate call is the array type
having the actual input type as elements.
</P
><P
> Here's the output using two different actual data types as arguments:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT attrelid::regclass, array_accum(attname)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_user'::regclass
GROUP BY attrelid;
attrelid | array_accum
----------+-----------------------------------------------------------------------------
pg_user | {usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil,useconfig}
(1 row)
SELECT attrelid::regclass, array_accum(atttypid)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_user'::regclass
GROUP BY attrelid;
attrelid | array_accum
----------+------------------------------
pg_user | {19,23,16,16,16,25,702,1009}
(1 row)</PRE
><P>
</P
><P
> For further details see the
<A
HREF="sql-createaggregate.html"
><I
>CREATE AGGREGATE</I
></A
>
command.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="xfunc-c.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="xtypes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>C-Language Functions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>User-Defined Types</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
