Re: Creating Aggregate functions in PLpgSQL

["Matthew Dennis" <mdennis@xxxxxxxxxx>]

On 12/11/07, Richard Broersma Jr <rabroersma@xxxxxxxxx> wrote:

Is it possible to create aggregate functions using pl/pgsql?

Yes, the sfunc and ffunc can be functions written in plpgsql.

If not possible in plpgsql, is there any other way to create these types of functions?

Yes, but I don't know the details (sorry for the near worthless answer)

If anyone could point to the correct documentation I would be most appreciative.

http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html is for 8.3 where there were some changes to the number of arguments an aggregate could take.  I believe it's also more descriptive documentation. 

In general, create a type to hold your state, a sfunc and a ffunc then create your aggregate pointing at those types and functions.

create type my_state as (
  my_sum bigint,
  my_count bigint
);

create or replace function my_avg_sfunc(state my_state, nextvalue bigint) returns my_state as $$
begin
  state.my_sum := state.my_sum + nextvalue;
  state.my_count := state.my_count + 1;
end; $$ language plpgsql;
 

create or replace function my_avg_ffunc(state my_state) returns float as $$
begin
  return state.my_sum::float / state.my_count::float ;
end; $$ language plpgsql;

create aggregate my_avg(bigint) (
  stype = my_state,
  sfunc = my_avg_sfunc,
  finalfunc = my_avg_ffunc,
  initcond = '(0, 0)'
);

of course for things like average you wouldn't need a custom type...