CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.1
SHl MRJ
SHl MRJ

Reputation: 73

Calculate MAX VALUE AND SUM AT THE SAME TIME

For my project I'm creating some customed faggregate function, which are used to apply differential privacy. So far, I was able to implement the MAX and MIN function, adding some Laplace noise. I'm having trouble with the SUM_LAPLACE aggregate. My upper bound (the sensivity), should be the absolute value of the MAX value in the column.

How can I calculte the max and the sum at the same time, and then pass two output value to a function?

CREATE OR REPLACE FUNCTION calculateSum(real,real,OUT real, OUT real) AS $$
DECLARE 
   sumValue real := 0;
       max_v real;
BEGIN
   IF $1 IS NULL THEN
    sumValue := sumValue + $2;
   ELSIF $2 IS NULL THEN
    sumValue := sumValue + $1;
   ELSIF $2 IS NULL AND $1 IS NULL THEN
    sumValue := sumValue;
   ELSE
    sumValue := $1 + $2;
   END IF;
       max_v = searchmaximumvalue($1,$2);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION addLaplacianNoiseSum(real) RETURNS real AS $$
DECLARE
    epsilon real := 1.2;
    sensivity real := (epsilon * 2) + ($1/2);
    laplaceDistribution real;
 BEGIN
 laplaceDistribution := sensivity / (epsilon);
 RETURN  $1 + laplaceDistribution;

 END;
 $$ LANGUAGE plpgsql;

CREATE AGGREGATE SUM_LAPLACE(real)
(
SFUNC = calculateSum,
STYPE = real,
FINALFUNC = addLaplacianNoiseSum
);

The function searchmaximumvalue($1,$2) works fine. I'd like to return from the first function the sum and max_v and pass them to the second function below. How can I do that?

Upvotes: 0

Views: 196

Answers (1)

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

You can create your own return type:

CREATE TYPE my_type AS (sum real, max_v real);

This can be used within the SFUNC: 

CREATE OR REPLACE FUNCTION calculateSum(my_type, real) RETURNS my_type  -- returns my_type
AS $$
DECLARE 
   sumValue real := 0;
   max_v real;
   output my_type; -- new variable of my_type
BEGIN
   /* A LOT OF CODE HERE */

   output.sum := sumValue;
   output.max_v := max_v;

   RETURN output;
END;
$$ LANGUAGE plpgsql;

And of course as input for your FINALFUNC:

CREATE OR REPLACE FUNCTION addLaplacianNoiseSum(my_type) RETURNS real AS $$

Using it within the FINALFUNC:

$1.max_v
$1.sum

Your aggregate then looks like

CREATE AGGREGATE SUM_LAPLACE(real) (
    SFUNC = calculateSum,
    STYPE = my_type,         -- return type == my_type
    FINALFUNC = addLaplacianNoiseSum
);

Upvotes: 1

Related Questions