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
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.