Writing Functions - PostgreSQL: Up and Running - page 146

A trigger function always outputs a data type called a trigger . Because PostgreSQL
trigger functions are no different from any other function, you can reuse the same trigger
function across different triggers. This is usually not the case for other databases, in
which each trigger has its own nonreusable handler code.
In PostgreSQL, each trigger must have exactly one associated triggering function to
handle the firing. To apply multiple triggering functions, you must create multiple trig‐
gers against the same event. The alphabetical order of the trigger name determines the
order of firing. Each trigger will have access to the revised data from the previous trigger.
Triggers themselves do not constitute separate transactions. If any trigger issues a roll‐
back, all data amended by earlier triggers fired by the same event will roll back.
You can use almost any language to create trigger functions, with SQL being the notable
exception. PL/pgSQL is by far the most popular language. We demonstrate writing
trigger functions using PL/pgSQL in “Writing Trigger Functions in PL/pgSQL” on page
152 .
Aggregates
Most other databases limit you to ANSI SQL built-in aggregate functions such as MIN ,
MAX , AVG , SUM , and COUNT . In PostgreSQL, you don't have this limit. If you need a more
esoteric aggregate function, you're welcome to write your own. Because you can use any
aggregate function in PostgreSQL as a window function (see “Window Functions” on
page 132 ), you can get twice the use out of any aggregate function that you author.
You can write aggregates in almost any language, SQL included. An aggregate is gen‐
erally composed of one or more functions. It must have at least a state transition function
to perform the computation; usually this function runs repeatedly to create a single
output from two input rows. You can also specify optional functions to manage initial
and final states. You can also use a different language for each of the subfunctions. We
have various examples of building aggregates using PL/pgSQL, PL/Python, and SQL in
the article PostgreSQL Aggregates .
Regardless of which language you use to code the functions, the glue that brings them
all together is the CREATE AGGREGATE command:
CREATE AGGREGATE my_agg ( input data type ) (
SFUNC= state function name ,
STYPE= state type ,
FINALFUNC= final function name ,
INITCOND= initial state value , SORTOP= sort_operator
);
The final function is optional, but if specified, it must take as input the result of the state
function. The state function always takes a data type as the input along with the result
of the last call to the state function. Sometimes this result is what you want as the result
of the aggregate function, and sometimes you want to run a final function to massage