Find SUM and MAX in custom aggregate function

Question

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.

Answer 1

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