User-Defined Aggregate Functions

In this lesson, you will learn how to extend PostgreSQL’s power by defining your own custom aggregate functions to perform specialized data reduction tasks.

Beyond SUM and AVG

Standard SQL provides built-in functions like SUM, COUNT, and AVG. However, sometimes you need to collapse data in ways that don't fit these defaults—for example, calculating the "geometric mean" of a series or concatenating strings with specific delimiters. A User-Defined Aggregate (UDA) allows you to encapsulate custom logic that processes a set of rows into a single scalar result.

The Mechanics: State and Transition

To create an aggregate in PostgreSQL, you must define two primary pieces of logic:

1. The State Transition Function: This function takes the current state and the next value from your table, then returns an updated state.
2. The Final Function (Optional): This takes the final state and performs a calculation to produce the ultimate result.

Think of this like a runner passing a baton. Each row is a runner who takes the "state" (the baton), adds their data to it, and passes it to the next row. Once the last row is processed, the final function shapes the baton into the data you need.

Simple Example: Geometric Mean

If you wanted to multiply a series of numbers and then take the root, you would define a transition function that multiplies the current state by the new value.

-- Step 1: Create the transition function
CREATE FUNCTION mul_state(numeric, numeric) RETURNS numeric AS $$
  SELECT $1 * $2;
$$ LANGUAGE SQL;

-- Step 2: Define the aggregate using the state function
CREATE AGGREGATE geometric_prod(numeric) (
  SFUNC = mul_state,
  STYPE = numeric,
  INITCOND = '1'
);

Once defined, you use geometric_prod() just like you would SUM(). When the database executes your query, it invokes your mul_state function for every row in the group, accumulating the product into the STYPE (numeric) memory context until the process is complete. This allows you to handle complex data reduction patterns that are otherwise impossible with standard SQL syntax.

Key Takeaway: User-Defined Aggregates allow you to transform sets of data into custom values by defining a state-transition logic that PostgreSQL applies row-by-row.