I've been working recently with custom aggregate function. In this custom aggregate, the first function, doesn't compute the sum and the max value correctly. I'm using a composite type to return the sum and the max value.
I've tried with appending everything with the array, but it's not an efficient way to work
CREATE TYPE sum_max_complex AS (sum real, max_v real);
CREATE OR REPLACE FUNCTION calculateSum(sum_max_complex, real) RETURNS sum_max_complex AS $$
DECLARE
sumValue real := 0;
max_v real := $2;
output sum_max_complex;
BEGIN
RAISE NOTICE '-------------------';
RAISE NOTICE 'IL PRIMO VALORE DI INPUT E: % ... %',$1.sum,$1.max_v;
RAISE NOTICE 'IL SECONDO VALORE DI INPUT E: %',$2;
IF $2 IS NOT NULL THEN
sumValue := calculateSumAggregate(sumValue,$2) + sumValue;
ELSE
sumValue := sumValue;
END IF;
max_v := searchmaximumvalue(max_v,$2);
output.sum := sumValue;
output.max_v := max_v;
RAISE NOTICE '-------------------';
RAISE NOTICE 'IL VALORE DI OUTPUT SONO: % ... %',output.sum,output.max_v;
RETURN output;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION addLaplacianNoiseSum(sum_max_complex) RETURNS real AS $$
DECLARE
epsilon real := 0.005;
sensivity real := $1.max_v;
laplaceDistribution real;
BEGIN
laplaceDistribution := generaterandomvalues(sensivity / (epsilon));
RETURN $1.sum + laplaceDistribution;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE SUM_LAPLACE(real)
(
SFUNC = calculateSum,
STYPE = sum_max_complex,
FINALFUNC = addLaplacianNoiseSum
);
In my table column, I have as values: 19,22,22.5,27. It takes the correct value in the $2 parameter method, in the 1st function, but doesn't accumulate and sum every value.
It doesn't look like you are ever adding to the values stored in the sum_max_complex type. Here's a simplified example that shows approximately what you should do. I don't know what calculateSumAggregate or generaterandomvalues do, so I wasn't able to reproduce those.
CREATE TYPE sum_max_complex AS (sum real, max_v real);
CREATE OR REPLACE FUNCTION calculateSum(sum_max_complex, real) RETURNS sum_max_complex AS $$
select ROW(
$1.sum + coalesce($2, 0),
greatest($1.max_v, $2)
)::sum_max_complex;
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION addLaplacianNoiseSum(sum_max_complex) RETURNS real AS $$
select $1.sum + ($1.max_v/0.005);
$$ LANGUAGE SQL IMMUTABLE;
CREATE AGGREGATE SUM_LAPLACE(real)
(
SFUNC = calculateSum,
STYPE = sum_max_complex,
FINALFUNC = addLaplacianNoiseSum,
INITCOND = '(0, 0)'
);
with a as (select a from (values (19), (22), (22.5), (27)) v(a))
select sum_laplace(a) from a;
sum_laplace
-------------
5490.5