Custom Agg Functions

my_avg.sql

CREATE TYPE avg_state AS (
  total numeric,
  count integer
);

CREATE OR REPLACE FUNCTION my_avg_state(state avg_state, value numeric)
RETURNS avg_state AS $$
BEGIN
  IF value IS NOT NULL THEN
    state.total := state.total + value;
    state.count := state.count + 1;
  END IF;
  RETURN state;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION my_avg_final(state avg_state)
RETURNS numeric AS $$
BEGIN
  IF state.count = 0 THEN
    RETURN NULL; -- To handle division by zero if there are no non-null values
  ELSE
    RETURN state.total / state.count;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE my_avg(numeric) (
  SFUNC = my_avg_state,
  STYPE = avg_state,
  FINALFUNC = my_avg_final,
  INITCOND = '(0,0)' -- Initialize total to 0 and count to 0
);