GNU/Linux man pages
Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :
GNU/Linux |
RedHat 5.2(Apollo) |
|
 |
create_aggregate(l) |
 |
create aggregate - define a new aggregate
create aggregate agg-name [as]
|
([sfunc1 = state-transition-function-1 | ||
|
, basetype = data-type | ||
|
, stype1 = sfunc1-return-type] | ||
|
[, sfunc2 = state-transition-function-2 | ||
|
, stype2 = sfunc2-return-type] | ||
|
[, finalfunc = final-function] | ||
|
[, initcond1 = initial-condition-1] | ||
|
[, initcond2 = initial-condition-2]) |
An aggregate
function can use up to three functions, two state
transition functions, X1 and X2:
X1( internal-state1, next-data_item ) --->
next-internal-state1
X2( internal-state2 ) ---> next-internal-state2
and a final calculation function, F:
F(internal-state1, internal-state2) ---> aggregate-value
These functions are required to have the following
properties:
The arguments to state-transition-function-1 must be (stype1,basetype), and its return value must be stype1.
The argument and return value of state-transition-function-2 must be stype2.
The arguments to the final-calculation-function must be (stype1,stype2), and its return value must be a POSTGRES base type (not necessarily the same as basetype.
The final-calculation-function should be specified if and only if both state-transition functions are specified.
Note that it is possible to specify aggregate functions that have varying combinations of state and final functions. For example, the “count” aggregate requires sfunc2 (an incrementing function) but not sfunc1 or finalfunc, whereas the “sum” aggregate requires sfunc1 (an addition function) but not sfunc2 or finalfunc and the “average” aggregate requires both of the above state functions as well as a finalfunc (a division function) to produce its answer. In any case, at least one state function must be defined, and any sfunc2 must have a corresponding initcond2.
Aggregates also require two initial conditions, one for each transition function. These are specified and stored in the database as fields of type text.
This avg
aggregate consists of two state transition functions, a
addition function and a incrementing function. These modify
the internal state of the aggregate through a running sum
and and the number of values seen so far. It accepts a new
employee salary, increments the count, and adds the new
salary to produce the next state. The state transition
functions must be passed correct initialization values. The
final calculation then divides the sum by the count to
produce the final answer.
--
--Create an aggregate for int4 average
--
create aggregate avg (sfunc1 = int4add, basetype = int4,
stype1 = int4, sfunc2 = int4inc, stype2 = int4,
finalfunc = int4div, initcond1 = "0", initcond2 =
"0")
create function(l), remove aggregate(l).
|
create_aggregate(l) | |