Senior LevelPostgreSQL
How do you create high-performance Custom Aggregates using sfunc, ffunc, and combinefunc in C/PLpgSQL?
❓ Interview Prompt: Explain `CREATE AGGREGATE`, State Transition Function (`SFUNC`), Final Function (`FFUNC`), and Parallel Combine Function (`COMBINEFUNC`).
PostgreSQL allows creating Custom Aggregates ('CREATE AGGREGATE') that execute specialized aggregation logic:
Components:
1
'SFUNC' (State Transition Function): Called for every row to update internal state.
2
'STYPE': Data type of the internal state variable.
3
'FFUNC' (Final Function): Called once to transform final state into the result.
4
'COMBINEFUNC': Merges state from parallel workers for parallel aggregation!
Code Implementation Example
sql
-- State transition function
CREATE FUNCTION avg_state(state NUMERIC[], val NUMERIC)
RETURNS NUMERIC[] AS $$
BEGIN
RETURN ARRAY[state[1] + val, state[2] + 1];
END;
$$ LANGUAGE plpgsql;
-- Final function
CREATE FUNCTION avg_final(state NUMERIC[])
RETURNS NUMERIC AS $$
BEGIN
RETURN state[1] / NULLIF(state[2], 0);
END;
$$ LANGUAGE plpgsql;
-- Custom aggregate
CREATE AGGREGATE custom_avg(NUMERIC) (
SFUNC = avg_state,
STYPE = NUMERIC[],
FINALFUNC = avg_final,
INITCOND = '{0,0}'
);💡Creating a custom average aggregate using SFUNC and FINALFUNC.
🎯 Interview Key Takeaway
Define custom COMBINEFUNC functions in custom aggregates to support multi-core parallel query execution.
#CREATE AGGREGATE#SFUNC#FFUNC#Custom Aggregates#Senior
Back to all PostgreSQL Questions→