CODEWITHSUNDEEP
sqlpostgresqlaggregateplpgsql
SHl MRJ
SHl MRJ

Reputation: 73

Find Min Or Max in custom aggregate function postgresql

I'm new to Postgresql (I'm programming in PL/pgSQL, there's no much difference with sql). I wrote my custome aggregate function, which has to find the min value, or max in an array of numeric. This is the function aggregate code

CREATE OR REPLACE FUNCTION searchMaxValue (numeric[]) RETURNS numeric AS $$
DECLARE 
i numeric;
maxVal numeric;
BEGIN
maxVal = $1[1];
IF ARRAY_LENGHT($1,1) > 0 THEN --Checking whether the array is empty or  not
   <<confrontoMassimo>>
   FOREACH i IN ARRAY $1 LOOP --Looping through the entire array, passed as parameter
       IF maxVal <= $1[i] THEN
           maxVal := $1[i];
       END IF;
   END LOOP confrontoMassimo;
   ELSE
   RAISE NOTICE 'Invalid parameter % passed to the aggregate function',$1;
   --Raising exception if the parameter passed as argument points to null.
   RAISE EXCEPTION 'Cannot find Max value. Parameter % is null', $1
   USING HINT = 'You cannot pass a null array! Check the passed parameter';

END IF;
RETURN maxVal;
END;
$$ LANGUAGE plpgsql;

 CREATE AGGREGATE searchMaxValueArray (numeric)
(
sfunc = array_append,
stype = numeric[],
finalfunc = searchMaxValue,
initCond = '{}'
);

The problem is, that it doesn't work as expected. What is the problem?

Upvotes: 0

Views: 560

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21346

As mentioned above, there's a small typo in your function; ARRAY_LENGHT should be ARRAY_LENGTH.

Aside from that, the only issue I can see is here:

FOREACH i IN ARRAY $1 LOOP
    IF maxVal <= $1[i] THEN
        ...

In a FOREACH loop, the target variable i isn't the array index, it's the array element itself. In other words, the loop should be:

FOREACH i IN ARRAY $1 LOOP
    IF maxVal <= i THEN
        maxVal := i;
    END IF;
END LOOP

With those changes, it seems to work as expected: https://rextester.com/FTWB14034

Upvotes: 2

Related Questions