Using aggregate function in custom function

Question

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.

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