Re: Aggregate functions with two or more arguments? - Mailing list pgsql-general

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