Re: Aggregate functions with two or more arguments?

Brian K Boonstra wrote:

> Let's say that I want to efficiently compute something like a weighted
> standard deviation (the actual formula I have in mind is slightly more
> complicated). The kind of SQL statement I want to have work is
> something like
>
> SELECT weighted_stdev( t.val, t.weight ) FROM target_vals_tbl t
> WHERE t.val > 0;
>
>
> I thought I'd like to write a C function (or more properly a set of two
> C functions) into the server side to handle this, and then declare it as
> an aggregate using CREATE AGGREGATE. However, aggregate functions
> appear to want just a single argument, so I feel like either I am on the
> wrong track, or I have run into a limitation of postgresql.

I'm not sure what the most elegant solution is, but when I've
encountered this scenario in the past, I created a custom type for
the aggregate. So this meant creating an input and output function
for the type, and then creating a function to return the type for
use in the aggregate. Example:

CREATE OR REPLACE FUNCTION tier_input(cstring) RETURNS tier
AS '/usr/local/pgsql/lib/tradedb.so'
LANGUAGE 'C' WITH (isStrict);

CREATE OR REPLACE FUNCTION tier_output(tier) RETURNS cstring
AS '/usr/local/pgsql/lib/tradedb.so'
LANGUAGE 'C' WITH (isStrict);

CREATE TYPE tier (
INTERNALLENGTH = 136,
INPUT = tier_input,
OUTPUT = tier_output
);

CREATE OR REPLACE FUNCTION to_tier(text, text, text, int4, int4)
RETURNS tier
AS '/usr/local/pgsql/lib/tradedb.so'
LANGUAGE 'C' WITH (isStrict);

CREATE OR REPLACE FUNCTION the_amount(tier) RETURNS text
AS '/usr/local/pgsql/lib/tradedb.so'
LANGUAGE 'C' WITH (isStrict);

CREATE OR REPLACE FUNCTION tier_s(tier, tier) RETURNS tier
AS '/usr/local/pgsql/lib/tradedb.so'
LANGUAGE 'C' WITH (isStrict);

CREATE OR REPLACE FUNCTION tier_f(tier) RETURNS tier
AS '/usr/local/pgsql/lib/tradedb.so'
LANGUAGE 'C' WITH (isStrict);

CREATE AGGREGATE tier_sum (
BASETYPE = tier,
SFUNC = tier_s,
STYPE = tier,
FINALFUNC = tier_f,
INITCOND = '0 0 temp_table 0 0'
);

And then I invoke the aggregate like:

SELECT tier_sum(to_tier(a, b, c, d, e))
FROM foo
WHERE bar;

HTH,

Mike Mascari