On 12/11/07, Richard Broersma Jr <rabroersma@yahoo.com> 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...