Building normally open floodgates

Yes, if you open it with a lever first, since the pressure plate won't send an initial "open" signal when built.

The pressure plate should be set to repeat (default) and to "activate" at a low water level, like 0-1. Activating means sending an "open" signal to the floodgate. The pressure plate will "deactivate" and send a "close" signal to the floodgate when the water level goes above the activation range.

Beware that floodgates are very slow and may be jammed by even the smallest objects, leaving you without a way to regulate intake!

A deep reservoir design I often deploy is to have the intake on a relatively high level compared to the reservoir or plumbing system itself, with the "valve"/floodgate near the intake and a fairly long pipe down to the reservoir itself. (Or perhaps just a hole with a hatch in the bottom of a river, dug through when frozen). Both before and after the floodgate I dig maintenance tunnels sealed by doors or raising bridges (a good choice instead of the floodgate itself if possible, since it won't jam). I put the pressure plate inside the reservoir itself on the second to highest level of the reservoir itself, so it'll trigger just before it gets full.

If I can't dig a big enough reservoir to contain all the water still in the intake pipe, I make an overflow channel somewhere so any excess water is just dumped out, decreasing the risks inherit in the closing delay.