Hi,
I am trying to debug a function that was written by someone else. As part of what this function does, it calculates the median value of a values passed on to it as a single column. The value that it return is wrong. The user written function, lets call it calculate median_room_rate. It selects a list of qualifying values and uses median to determine the median. At first I thought this was a inbuilt SQL function such as min and max. After many hours of trying to troubleshoot I discovered median might be a user written or an extension (I still don't know) function. My pgAdmin browser was not enabled to display the Aggregates Object where I found it. median is defines as follows:
CREATE AGGREGATE public.median(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=array_median
I will be honest. I have no clue how that works or what it is supposed to do.
As an example, I am passing on two values 0.1303 and 0.0757. These are being cast as According to online calculators and Excel I am supposed to get back 0.1030. However, the median function is returning 1.0757 which totally does not make sense.
I tried to create my own median function in the Aggregates Object thinking I will pass in a numeric[] object, but I am unable to select any State Function or Final Function in the UI.
I suppose I can write my own function, but not sure how/what.
Any help is mightily appreciated.
Chandru
