-- Create a custom aggregate for geometric mean
CREATE OR REPLACE FUNCTION geo_mean_sfunc(state FLOAT8[], val FLOAT8)
RETURNS FLOAT8[] AS $$
BEGIN
  RETURN ARRAY[state[1] + LN(val), state[2] + 1];
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION geo_mean_finalfunc(state FLOAT8[])
RETURNS FLOAT8 AS $$
BEGIN
  RETURN EXP(state[1] / state[2]);
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE geo_mean(FLOAT8) (
  SFUNC = geo_mean_sfunc,
  STYPE = FLOAT8[],
  INITCOND = '{0, 0}',
  FINALFUNC = geo_mean_finalfunc
);

SELECT dept_id, geo_mean(salary) AS geometric_mean_salary
FROM employees GROUP BY dept_id;