I've a feeling that any useful waterfall will depressurise any water you let into the top, regardless of how viable the initial pressure is to getting water back to the level you want.
(Ninjaed on this point by Sphalerite)Any reason you want it to flow back into the river? Could you send it into a bit of useless (or 'drain-off-the-edge'able) bit of cavern? Edge-connected cavern sea? Out through edge-fortifications? Into magma to create obsidian? Just into magma, the resulting obsidian falling to destruction on the SMR or just being ignored apart from rubble and FPS-eater? There's all kinds of ways to rid yourself of the waterfall outflow, apart from just digging a WIIIIIIIIIDE cistern for it to drain into and evaporate at the edges so that it never fills up.
If you're wanting the water back up at the top (in the river, or anywhere else), I'd look at pumping it back up. Windmill(s)-powered pump-stack. (Note recent threads about them collapsing... I'd only ever build pump-stacks strictly from the bottom up, even though you
can designated to build a second pump over a pre-existing pump-yet-to-be-built.) Other ways are possible (pump-steps, each of them being worked by the power of a waterwheel on the inward side, similar to the Perpetual Motion machines except without the looping water) if your wind or easy accumulation of multi-windmill power isn't sufficient.
But it all depends on what you want.
The FUN (or
‼FUN‼, or perhaps more likely
≈FUN≈) you will encounter will probably be because you didn't anticipate the water building up where it ends up doing so, and spilling into the rest of your complex. Build doors, floodgates, and in case they fail, prepare to set down other emergency walls slightly further away (with enough time to get them started before it becomes Dangerous Ground) to permanently seal the area off, and work out which dwarfs are going to be potentially expendible. If it's your first time trying to breach a watersource, there's plenty of reason to suppose you'll miss something. This also applies if it's not, of course.