So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior.
Instead of writing unit tests for such trivial things as updating one row and testing if it got updated, it's better to make such unit tests asserts instead, which is exactly what we achieve if we provide a syntax to throw an error if not exactly 1 row was affected.
Marko posted a patch to add assertions to PL/pgSQL last year, see http://www.postgresql.org/message-id/5234AF3F.4000409@joh.to. It was a long thread, but in the end I think everyone was more or less OK with the syntax "ASSERT <condition>;". I also think that syntax is fine, and it would be a nice feature, assuming we can avoid reserving the ASSERT keyword.
I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after the update.
