Point-in-polygon: one PostGIS query for geofencing, zones, and spatial lookups

You've got a coordinate. You've got a pile of regions on a map. You need to know: which region does this point fall in, or is it inside one at all? That single question hides inside a lot of features:...