There are various forms of ST_Value and the one that will probably help you is the ST_Value(rast, band, x, y) one.
Modifying one of the examples from the docs, you can dump all the values for each pixel using generate_series in the x and y direction, and then use group by (or distinct) on those to get the list of unique pixel values.
SELECT count(b1val), b1val FROM
(SELECT ST_Value(rast, 1, x, y) As b1val
FROM dummy_rast CROSS JOIN
generate_series(1, raster_width) As x
CROSS JOIN generate_series(1, raster_height) As y
WHERE rid = raster_id) as rast_values
group by b1val;
where you will need to provide values for raster_id, raster_width and raster_height and I have assumed you are looking at band 1.