Wow, so much detailed information and meaningful responses. I am humbled, and almost not worthy of sticking in my 2 cents. But I can’t resist. I am not trying to argue with any previous psak, I really just intend to provide technical information:
1. The term ‘Water Fountain’ is generic, and could mean many things. Most of the responses here seem to be referring to what is actually a ‘Water Cooler’. In other words, just a ‘fountain’ could mean a dispenser with a faucet, like you find in NYC parks. No electric whatsoever. If so, in New York City, where we have good water pressure and there are no pumps involved, a simple fountain with non-electric valve (whether it be a faucet, button, bar, or lever) should pose no Shabbos issue, other that the filtering question that exists in many homes. And if unfiltered, another set of shailos, albeit not having to do with Shabbos.
Although it is not found in NYC, I imagine that there might be places that need a pump to provide even plain water at any pressure, whether it be from a holding tank, or even a well. (Maybe in a bungalow colony?) That could be a shaila too, but really has nothing per se to do with a water fountain, as it would apply even more commonly to a household sink.
2. Once you transition into the realm of Water Coolers, there are many different types. Some modern ones are completely electronic, including a dispenser button. Clearly a problem.
3. On the simpler water coolers that are either fed by municipal water pressure, or a gravity fed spring water bottle, there is usually a holding tank that is kept cold. The process of dispensing water is typically completely non-electric, and does not directly activate anything. When the warm new water enters the tank, it could trigger the thermostat to turn on the compressor. That is an indirect action, but is certainly agreeable to the consumer. So the question would be if it is considered a Psik Reisha or not. One thing I think not mentioned by previous posters, is that it really ought to depend on the size of the holding tank, the quantity of water that you are removing (and thus replacing with new uncooled water) and the temperature of the newly entering water. Some coolers do not turn on until many cups have been removed. Furthermore, there might even be a purposeful delay built into the thermostat to extend the life of the compressor (preventing it from turning off and then on again too soon.) This could all change whether it is a Psik Reisha or not.