I think you could get this to work, the thing would be balancing the flows - basically, make sure that you empty the fountain bowl at least as fast as water comes into the fountain. Depending on the amount of water pressure, this could be tricky. Or is there just "pressurized" and "not pressurized" water, with eg. the height difference of the u-bend making no difference on the end result.
Elementary considerations include remotely powering the pumps. At least the emptying one. Also including shutoff levers for both the input (floodgates?) and the output pump, so you can empty the bowl if necessary. Most importantly, to avoid flooding the fortress, initially build the dining room/meeting area/hallway/whatever where you'll have the fountain so that there's only 1 exit, with a raising bridge that forms a wall to make it an isolated cube.
I suppose you could just wall it in for the test run once you think it's finished, but a bridge is quicker to reuse if you end up having to make several modifications, can come in handy later to seal dwarves or enemies in, can be used as an emergency shutoff to prevent the entire fort flooding if something, eg. the power to the emptying pump, breaks, and admiring bridges gives happy thoughts (make it out of gold!). If you later add more exits from the room, you may want to consider having raising bridges there as well, for the emergency shutoff/saferoom uses.
An easier way to do this would just to take the water from an aquifer or maybe a river/natural lake/ocean, drop it a few z-levels, route it to the fountain, then drain the output from the fountain off the map or into another aquifer layer. No pumps necessary. I probably would've done something this with my cistern in my latest map, if it wasn't for the fact that the aquifer is salty water = had to have a pump to desalinate.