How to Create Aggregate Function in PostgreSQL: Syntax and Example

In PostgreSQL, you create a custom aggregate function using the CREATE AGGREGATE statement, which requires a state transition function and a state data type. This lets you define how input values are combined step-by-step to produce a final aggregated result.

Syntax

The CREATE AGGREGATE statement defines a new aggregate function by specifying:

• SFUNC: the function called for each input value to update the aggregate state
• STYPE: the data type of the state value that holds intermediate results
• FINALFUNC (optional): a function to compute the final result from the state
• INITCOND (optional): initial value for the state

sql

CREATE AGGREGATE name (input_data_type) (
  SFUNC = state_function,
  STYPE = state_data_type,
  FINALFUNC = final_function,  -- optional
  INITCOND = 'initial_condition' -- optional
);

Example

This example creates a simple aggregate function my_sum that sums integer values by using the built-in int4pl function as the state function and integer as the state type.

sql

CREATE AGGREGATE my_sum(integer) (
  SFUNC = int4pl,
  STYPE = integer,
  INITCOND = '0'
);

-- Using the aggregate
SELECT my_sum(value) FROM (VALUES (1), (2), (3), (4)) AS t(value);

Output

 my_sum 
--------
     10

Common Pitfalls

Common mistakes when creating aggregates include:

• Not specifying the correct STYPE matching the state function output.
• Omitting INITCOND when the state function requires an initial value.
• Using a state function that does not accept the state type as the first argument.
• Forgetting to define a FINALFUNC when the final result differs from the state type.

sql

/* Wrong: state function returns a different type than STYPE */
CREATE AGGREGATE wrong_agg(integer) (
  SFUNC = abs,  -- abs returns integer but expects integer input, not state
  STYPE = integer
);

/* Right: use a proper state function like int4pl */
CREATE AGGREGATE correct_agg(integer) (
  SFUNC = int4pl,
  STYPE = integer,
  INITCOND = '0'
);

Quick Reference

Summary tips for creating aggregates:

• SFUNC: must take (state, input) and return new state
• STYPE: type of the state value
• FINALFUNC: optional, transforms state to final output
• INITCOND: optional, initial state value as text
• Test your aggregate with simple queries to verify correctness

Key Takeaways

Use CREATE AGGREGATE with SFUNC and STYPE to define custom aggregates in PostgreSQL.

The state function updates the aggregate state for each input row.

Specify INITCOND if your state function needs an initial value.

Use FINALFUNC if the final output differs from the state type.

Test your aggregate function with sample data to ensure it works as expected.