I686LINUX/util/I686LINUX/doc/postgresql/html/xaggr.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
   2 <HTML
   3 ><HEAD
   4 ><TITLE
   5 >User-Defined Aggregates</TITLE
   6 ><META
   7 NAME="GENERATOR"
   8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
   9 REV="MADE"
  10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
  11 REL="HOME"
  12 TITLE="PostgreSQL 7.4.1 Documentation"
  13 HREF="index.html"><LINK
  14 REL="UP"
  15 TITLE="Extending SQL"
  16 HREF="extend.html"><LINK
  17 REL="PREVIOUS"
  18 TITLE="Function Overloading"
  19 HREF="xfunc-overload.html"><LINK
  20 REL="NEXT"
  21 TITLE="User-Defined Types"
  22 HREF="xtypes.html"><LINK
  23 REL="STYLESHEET"
  24 TYPE="text/css"
  25 HREF="stylesheet.css"><META
  26 NAME="creation"
  27 CONTENT="2003-12-22T03:48:47"></HEAD
  28 ><BODY
  29 CLASS="SECT1"
  30 ><DIV
  31 CLASS="NAVHEADER"
  32 ><TABLE
  33 SUMMARY="Header navigation table"
  34 WIDTH="100%"
  35 BORDER="0"
  36 CELLPADDING="0"
  37 CELLSPACING="0"
  38 ><TR
  39 ><TH
  40 COLSPAN="5"
  41 ALIGN="center"
  42 VALIGN="bottom"
  43 >PostgreSQL 7.4.1 Documentation</TH
  44 ></TR
  45 ><TR
  46 ><TD
  47 WIDTH="10%"
  48 ALIGN="left"
  49 VALIGN="top"
  50 ><A
  51 HREF="xfunc-overload.html"
  52 ACCESSKEY="P"
  53 >Prev</A
  54 ></TD
  55 ><TD
  56 WIDTH="10%"
  57 ALIGN="left"
  58 VALIGN="top"
  59 ><A
  60 HREF="extend.html"
  61 >Fast Backward</A
  62 ></TD
  63 ><TD
  64 WIDTH="60%"
  65 ALIGN="center"
  66 VALIGN="bottom"
  67 >Chapter 33. Extending <ACRONYM
  68 CLASS="ACRONYM"
  69 >SQL</ACRONYM
  70 ></TD
  71 ><TD
  72 WIDTH="10%"
  73 ALIGN="right"
  74 VALIGN="top"
  75 ><A
  76 HREF="extend.html"
  77 >Fast Forward</A
  78 ></TD
  79 ><TD
  80 WIDTH="10%"
  81 ALIGN="right"
  82 VALIGN="top"
  83 ><A
  84 HREF="xtypes.html"
  85 ACCESSKEY="N"
  86 >Next</A
  87 ></TD
  88 ></TR
  89 ></TABLE
  90 ><HR
  91 ALIGN="LEFT"
  92 WIDTH="100%"></DIV
  93 ><DIV
  94 CLASS="SECT1"
  95 ><H1
  96 CLASS="SECT1"
  97 ><A
  98 NAME="XAGGR"
  99 >33.9. User-Defined Aggregates</A
 100 ></H1
 101 ><A
 102 NAME="AEN29729"
 103 ></A
 104 ><P
 105 >   Aggregate functions  in <SPAN
 106 CLASS="PRODUCTNAME"
 107 >PostgreSQL</SPAN
 108 >
 109    are expressed as <I
 110 CLASS="FIRSTTERM"
 111 >state values</I
 112 >
 113    and <I
 114 CLASS="FIRSTTERM"
 115 >state transition functions</I
 116 >.
 117    That is,  an  aggregate  can  be
 118    defined  in terms of state that is modified whenever an
 119    input item is processed.  To define a new aggregate
 120    function, one selects a data type for the state value,
 121    an initial value for the state, and a state transition
 122    function.  The state transition function is just an
 123    ordinary function that could also be used outside the
 124    context of the aggregate.  A <I
 125 CLASS="FIRSTTERM"
 126 >final function</I
 127 >
 128    can also be specified, in case the desired result of the aggregate
 129    is different from the data that needs to be kept in the running
 130    state value.
 131   </P
 132 ><P
 133 >   Thus, in addition to the argument and result data types seen by a user
 134    of the aggregate, there is an internal state-value data type that
 135    may be different from both the argument and result types.
 136   </P
 137 ><P
 138 >   If we define an aggregate that does not use a final function,
 139    we have an aggregate that computes a running function of
 140    the column values from each row.  <CODE
 141 CLASS="FUNCTION"
 142 >sum</CODE
 143 >  is  an
 144    example  of  this  kind  of aggregate.  <CODE
 145 CLASS="FUNCTION"
 146 >sum</CODE
 147 > starts at
 148    zero and always adds the current  row's  value  to
 149    its  running  total.  For example, if we want to make a <CODE
 150 CLASS="FUNCTION"
 151 >sum</CODE
 152 >
 153    aggregate to work on a data type for complex numbers,
 154    we only need the addition function for that data type.
 155    The aggregate definition would be:
 156
 157 </P><PRE
 158 CLASS="SCREEN"
 159 >CREATE AGGREGATE complex_sum (
 160     sfunc = complex_add,
 161     basetype = complex,
 162     stype = complex,
 163     initcond = '(0,0)'
 164 );
 165
 166 SELECT complex_sum(a) FROM test_complex;
 167
 168  complex_sum
 169 -------------
 170  (34,53.9)</PRE
 171 ><P>
 172
 173    (In practice, we'd just name the aggregate <CODE
 174 CLASS="FUNCTION"
 175 >sum</CODE
 176 > and rely on
 177    <SPAN
 178 CLASS="PRODUCTNAME"
 179 >PostgreSQL</SPAN
 180 > to figure out which kind
 181    of sum to apply to a column of type <TT
 182 CLASS="TYPE"
 183 >complex</TT
 184 >.)
 185   </P
 186 ><P
 187 >   The above definition of <CODE
 188 CLASS="FUNCTION"
 189 >sum</CODE
 190 > will return zero (the initial
 191    state condition) if there are no nonnull input values.
 192    Perhaps we want to return null in that case instead --- the SQL standard
 193    expects <CODE
 194 CLASS="FUNCTION"
 195 >sum</CODE
 196 > to behave that way.  We can do this simply by
 197    omitting the <TT
 198 CLASS="LITERAL"
 199 >initcond</TT
 200 > phrase, so that the initial state
 201    condition is null.  Ordinarily this would mean that the <TT
 202 CLASS="LITERAL"
 203 >sfunc</TT
 204 >
 205    would need to check for a null state-condition input, but for
 206    <CODE
 207 CLASS="FUNCTION"
 208 >sum</CODE
 209 > and some other simple aggregates like
 210    <CODE
 211 CLASS="FUNCTION"
 212 >max</CODE
 213 > and <CODE
 214 CLASS="FUNCTION"
 215 >min</CODE
 216 >,
 217    it is sufficient to insert the first nonnull input value into
 218    the state variable and then start applying the transition function
 219    at the second nonnull input value.  <SPAN
 220 CLASS="PRODUCTNAME"
 221 >PostgreSQL</SPAN
 222 >
 223    will do that automatically if the initial condition is null and
 224    the transition function is marked <SPAN
 225 CLASS="QUOTE"
 226 >"strict"</SPAN
 227 > (i.e., not to be called
 228    for null inputs).
 229   </P
 230 ><P
 231 >   Another bit of default behavior for a <SPAN
 232 CLASS="QUOTE"
 233 >"strict"</SPAN
 234 > transition function
 235    is that the previous state value is retained unchanged whenever a
 236    null input value is encountered.  Thus, null values are ignored.  If you
 237    need some other behavior for null inputs, just do not define your transition
 238    function as strict, and code it to test for null inputs and do
 239    whatever is needed.
 240   </P
 241 ><P
 242 >   <CODE
 243 CLASS="FUNCTION"
 244 >avg</CODE
 245 > (average) is a more complex example of an aggregate.  It requires
 246    two pieces of running state: the sum of the inputs and the count
 247    of the number of inputs.  The final result is obtained by dividing
 248    these quantities.  Average is typically implemented by using a
 249    two-element array as the state value.  For example,
 250    the built-in implementation of <CODE
 251 CLASS="FUNCTION"
 252 >avg(float8)</CODE
 253 >
 254    looks like:
 255
 256 </P><PRE
 257 CLASS="PROGRAMLISTING"
 258 >CREATE AGGREGATE avg (
 259     sfunc = float8_accum,
 260     basetype = float8,
 261     stype = float8[],
 262     finalfunc = float8_avg,
 263     initcond = '{0,0}'
 264 );</PRE
 265 ><P>
 266   </P
 267 ><P
 268 >   Aggregate functions may use polymorphic
 269    state transition functions or final functions, so that the same functions
 270    can be used to implement multiple aggregates.
 271    See <A
 272 HREF="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC"
 273 >Section 33.2.5</A
 274 >
 275    for an explanation of polymorphic functions.
 276    Going a step further, the aggregate function itself may be specified
 277    with a polymorphic base type and state type, allowing a single
 278    aggregate definition to serve for multiple input data types.
 279    Here is an example of a polymorphic aggregate:
 280
 281 </P><PRE
 282 CLASS="PROGRAMLISTING"
 283 >CREATE AGGREGATE array_accum (
 284     sfunc = array_append,
 285     basetype = anyelement,
 286     stype = anyarray,
 287     initcond = '{}'
 288 );</PRE
 289 ><P>
 290
 291    Here, the actual state type for any aggregate call is the array type
 292    having the actual input type as elements.
 293   </P
 294 ><P
 295 >   Here's the output using two different actual data types as arguments:
 296
 297 </P><PRE
 298 CLASS="PROGRAMLISTING"
 299 >SELECT attrelid::regclass, array_accum(attname)
 300     FROM pg_attribute
 301     WHERE attnum &#62; 0 AND attrelid = 'pg_user'::regclass
 302     GROUP BY attrelid;
 303
 304  attrelid |                                 array_accum
 305 ----------+-----------------------------------------------------------------------------
 306  pg_user  | {usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil,useconfig}
 307 (1 row)
 308
 309 SELECT attrelid::regclass, array_accum(atttypid)
 310     FROM pg_attribute
 311     WHERE attnum &#62; 0 AND attrelid = 'pg_user'::regclass
 312     GROUP BY attrelid;
 313
 314  attrelid |         array_accum
 315 ----------+------------------------------
 316  pg_user  | {19,23,16,16,16,25,702,1009}
 317 (1 row)</PRE
 318 ><P>
 319   </P
 320 ><P
 321 >   For further details see the
 322    <A
 323 HREF="sql-createaggregate.html"
 324 ><I
 325 >CREATE AGGREGATE</I
 326 ></A
 327 >
 328    command.
 329   </P
 330 ></DIV
 331 ><DIV
 332 CLASS="NAVFOOTER"
 333 ><HR
 334 ALIGN="LEFT"
 335 WIDTH="100%"><TABLE
 336 SUMMARY="Footer navigation table"
 337 WIDTH="100%"
 338 BORDER="0"
 339 CELLPADDING="0"
 340 CELLSPACING="0"
 341 ><TR
 342 ><TD
 343 WIDTH="33%"
 344 ALIGN="left"
 345 VALIGN="top"
 346 ><A
 347 HREF="xfunc-overload.html"
 348 ACCESSKEY="P"
 349 >Prev</A
 350 ></TD
 351 ><TD
 352 WIDTH="34%"
 353 ALIGN="center"
 354 VALIGN="top"
 355 ><A
 356 HREF="index.html"
 357 ACCESSKEY="H"
 358 >Home</A
 359 ></TD
 360 ><TD
 361 WIDTH="33%"
 362 ALIGN="right"
 363 VALIGN="top"
 364 ><A
 365 HREF="xtypes.html"
 366 ACCESSKEY="N"
 367 >Next</A
 368 ></TD
 369 ></TR
 370 ><TR
 371 ><TD
 372 WIDTH="33%"
 373 ALIGN="left"
 374 VALIGN="top"
 375 >Function Overloading</TD
 376 ><TD
 377 WIDTH="34%"
 378 ALIGN="center"
 379 VALIGN="top"
 380 ><A
 381 HREF="extend.html"
 382 ACCESSKEY="U"
 383 >Up</A
 384 ></TD
 385 ><TD
 386 WIDTH="33%"
 387 ALIGN="right"
 388 VALIGN="top"
 389 >User-Defined Types</TD
 390 ></TR
 391 ></TABLE
 392 ></DIV
 393 ></BODY
 394 ></HTML
 395 >