Find Min Or Max in custom aggregate function postgresql

Question

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?

" it doesn't work as expected." is not a great problem description. Whether it throws any error? Does it give different results than expected? If so, show some example of what's expected and what you got from the function, etc. — Kaushik Nayak
– Kaushik Nayak, Commented Mar 6, 2019 at 14:39
ARRAY_LENGHT should be ARRAY_LENGTH. Is it just a typo or your issue is related to that ? — Kaushik Nayak
– Kaushik Nayak, Commented Mar 6, 2019 at 14:49
Can't you just use unnest along with MAX/MIN to accomplish this? i.e.: select MAX(id) FROM unnest(ARRAY[1,2,3]) AS t(id); — Andrew Whitaker
– Andrew Whitaker, Commented Mar 6, 2019 at 14:55
"I'm new to Postgres" and "I wrote my custom aggregate function" are just not two things one sees together very much. Writing a custom aggregation function is one of the more complicated things to do in any database. — Gordon Linoff
– Gordon Linoff, Commented Mar 6, 2019 at 15:06
You should perform the calculation of the maximum in sfunc rather than in finalfunc, the array could become too large to handle. I see no fundamental problem with your function, please describe the malfunction. — Laurenz Albe
– Laurenz Albe, Commented Mar 6, 2019 at 16:07

Nick Barnes · Accepted Answer · 2019-03-06 16:08:04Z

2

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

answered Mar 6, 2019 at 16:08

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Barnes Over a year ago

Though as @Laurenz mentioned above, this is a very inefficient way of approaching it. Not only are you potentially building huge arrays, you're copying the whole array every time you append a new value. But I suppose if you wanted something efficient, you'd just call MAX() ;)

SHl MRJ Over a year ago

That's what i was looking for. I thought that it worked like Java. Thanks, I fixed the issue as you suggested, and it's ok!

Collectives™ on Stack Overflow

Find Min Or Max in custom aggregate function postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related