On 12/11/07, Richard Broersma Jr <[EMAIL PROTECTED]> 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...
