So basically a statue with a water spout?
Nice. It could give an added happy thought if used in a statue garden, or a bedroom:
"Was soothed by flowing water lately" or
"Enjoyed the sight of flowing water" or if the dwarf likes fountains
"Was entranced by a water fountain"
The fountian should have a direction applied to it; sort of like archery ranges, it must be given a direction to throw it's water. If the space in front of it isn't a channel, the fountain generates a puddle and then a pool of water, which dwarves could slip on and bruise /break limbs. A channel that's in front of the statue would perminately become a pond, as long as the fountain remains.
Masterpeice fountains (has to be masterpeice; exceptional doesn't cut it) could also give a small chance (5%?) to return an insane dwarf* to a sane level, but they'd still have to serve prison time for their actions -- better then another dead dwarf at least. Hell, you could even make steel fountains that spew lava into a pond of lava (the only way to make one) filled with fire snakes ... Now that'd give one hell of a sweet appearance to the fortress entrance!
Of course, it needs a downside. Like, if the channel that feeds the fountain becomes blood or vomit filled, the fountain will spew blood and vomit -- possibly even miasma as the vomit works its way through the fountain. Obviously, enemies would also be able to come up through the channel.
Dwarves should still prefer to drink from wells though, but the channel that's used as a pond could be drunk from.
... Lol, artifact fountains that dispense liquior.
*berserking /babbling /etc