Custom Aggregate Type Support

In this lesson, you will learn how to define custom aggregate functions to perform complex data reductions on your own domain-specific types.

The Core Concept

Standard SQL gives you SUM, AVG, and COUNT. But what happens when your custom type—like a geometric shape or a complex financial object—doesn't fit into a simple mathematical sum? PostgreSQL allows you to define custom aggregates that act as a "state machine," processing rows one by one to distill a final result.

To create one, you define two main functions:

1. State Transition Function: Called for every row, it takes the current accumulated value and the new row's value, returning an updated state.
2. Final Function: Optionally called at the end to perform a final calculation on the accumulated state (like dividing a sum by a count).

A Practical Example: Aggregating Custom Types

Imagine you have a custom type coord representing a 2D point, and you want to find the "Center of Mass" for a set of points by averaging their coordinates.

First, you define a state transition function add_point(point, point) that adds the coordinates, and then you define the aggregate:

CREATE AGGREGATE centroid(coord) (
    SFUNC = add_point,      -- Transition function
    STYPE = coord,          -- The type used to hold intermediate state
    FINALFUNC = divide_point, -- Final calculation (dividing by count)
    INITCOND = '(0,0)'      -- Starting state
);

By specifying STYPE, you instruct the engine to allocate memory for your custom type during the aggregation process. This allows you to perform complex reductions—like concatenating strings, calculating standard deviations of geometric vectors, or merging JSONB objects—directly within a GROUP BY query.

This mechanism is particularly powerful because it respects the PARALLEL safety properties you’ve already mastered, allowing PostgreSQL to distribute the heavy lifting of your custom logic across multiple background workers. When you build these aggregates, ensure your transition function is efficient, as it is executed for every row in the set.

Key Takeaway: Custom aggregates transform your domain-specific types into first-class citizens in SQL, enabling sophisticated data reduction through state transition functions.