1

I need to calculate the rms (root mean square) for different columns. To not repeat myself I would like to create a custom aggregate function rms(numeric) which should do the following calculation:

SELECT SQRT(AVG(POWER(v, 2))) AS rms
FROM (VALUES (1), (2), (3), (4), (5)) AS vals (v);

The function should be called like the following:

SELECT RMS(v)
FROM (VALUES (1), (2), (3), (4), (5)) AS vals (v);

In both cases the output should be a floating number. In my example 3.3166....

I found several easy examples on how to create custom aggregate functions, but I could not find an example which explains how to use another aggregate function (in my case AVG()) within the custom aggregate function.

I also found the question (https://stackoverflow.com/questions/30667245/writing-my-own-aggregate-function-in-postgresql), which asks essentially the same thing. But the accepted answer recreates MAX. I would prefer not to recreate existing functions.

1

You would not use another aggregate function. You would define your aggregate "from scratch". I am surprised there is not an existing aggregate that does this, but I have not found one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.