<!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.3.6 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="2009-02-03T04:34:16"></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.3.6 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 34. 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"
>34.10. User-Defined Aggregates</A
></H1
><A
NAME="AEN41523"
></A
><P
> Aggregate functions in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
are expressed in terms of <I
CLASS="FIRSTTERM"
>state values</I
>
and <I
CLASS="FIRSTTERM"
>state transition functions</I
>.
That is, an aggregate operates using a state value that is updated
as each successive input row 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
might 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 sum (complex)
(
sfunc = complex_add,
stype = complex,
initcond = '(0,0)'
);
SELECT sum(a) FROM test_complex;
sum
-----------
(34,53.9)</PRE
><P>
(Notice that we are relying on function overloading: there is more than
one aggregate named <CODE
CLASS="FUNCTION"
>sum</CODE
>, but
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> can figure out which kind
of sum applies 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, do not declare your
transition function as strict; instead 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 (float8)
(
sfunc = float8_accum,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0}'
);</PRE
><P>
</P
><P
> Aggregate functions can 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 34.2.5</A
>
for an explanation of polymorphic functions.
Going a step further, the aggregate function itself can be specified
with polymorphic input type(s) 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 (anyelement)
(
sfunc = array_append,
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_tablespace'::regclass
GROUP BY attrelid;
attrelid | array_accum
---------------+---------------------------------------
pg_tablespace | {spcname,spcowner,spclocation,spcacl}
(1 row)
SELECT attrelid::regclass, array_accum(atttypid)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
GROUP BY attrelid;
attrelid | array_accum
---------------+-----------------
pg_tablespace | {19,26,25,1034}
(1 row)</PRE
><P>
</P
><P
> A function written in C can detect that it is being called as an
aggregate transition or final function by seeing if it was passed
an <TT
CLASS="STRUCTNAME"
>AggState</TT
> node as the function call <SPAN
CLASS="QUOTE"
>"context"</SPAN
>,
for example by:
</P><PRE
CLASS="PROGRAMLISTING"
> if (fcinfo->context && IsA(fcinfo->context, AggState))</PRE
><P>
One reason for checking this is that when it is true, the first input
must be a temporary transition value and can therefore safely be modified
in-place rather than allocating a new copy. (This is the <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>only</I
></SPAN
>
case where it is safe for a function to modify a pass-by-reference input.)
See <TT
CLASS="LITERAL"
>int8inc()</TT
> for an example.
</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
>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
bab02a23fa9f3df8d66a9a3231b50245
>
files
>
918
