This can be written as a set of MapAlgebra callback functions (one per particular algorithm) and a ST_Grayscale user (wrapper) function.
Basic implementation should just use the following:
Y = 0.2989 * R + 0.5870 * G + 0.1140 * B
Map Algebra callback function
ST_Grayscale4ma(double precision[][][] value, integer[][] pos, text[] VARIADIC userargs)
User function
ST_Grayscale(raster rast, integer redband=1, integer blueband=2, integer greenband=3)
Bands provided should be preprocessed before grayscaling to make sure input pixel types are 8BUI (via ST_Reclass if necessary)