Re: Creating Aggregate functions in PLpgSQL

On 12/11/07, Richard Broersma Jr <rabroersma(at)yahoo(dot)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...