Proposal: PL/PgSQL strict_mode

Hi,

After my previous suggestion for adding a STRICT keyword got shot
down[1], I've been thinking about an idea Andrew Gierth tossed out:
adding a new "strict mode" into PL/PgSQL.  In this mode, any query which
processes more than one row will raise an exception.  This is a bit
similar to specifying INTO STRICT .. for every statement, except
processing no rows does not trigger the exception.  The need for this
mode comes from a few observations I make almost every day:
   1) The majority of statements only deal with exactly 0 or 1 rows.
   2) Checking row_count for a statement is ugly and cumbersome, so
often it just isn't checked.  I often use RETURNING TRUE INTO STRICT _OK
for DML, but that a) requires an extra variable, and b) isn't possible
if 0 rows affected is not an error in the application logic.
   3) SELECT .. INTO only fetches one row and ignores the rest.  Even
row_count is always set to 0 or 1, so there's no way to fetch a value
*and* to check that there would not have been more rows.  This creates
bugs which make your queries return wrong results and which could go
undetected for a long time.

Attached is a proof-of-concept patch (no docs, probably some actual code
problems too) to implement this as a compile option:

=# create or replace function footest() returns void as $$
$# #strict_mode strict
$# begin
$#   -- not allowed to delete more than one row
$#   delete from foo where f1 < 100;
$# end$$ language plpgsql;
CREATE FUNCTION
=# select footest();
ERROR:  query processed more than one row
CONTEXT:  PL/pgSQL function footest() line 5 at SQL statement

Now while I think this is a step into the right direction, I do have a
couple of problems with this patch:
   1) I'm not sure what would be the correct behaviour with EXECUTE.
I'm tempted to just leave EXECUTE alone, as it has slightly different
rules anyway.
   2) If you're running in strict mode and you want to
insert/update/delete more than one row, things get a bit uglier; a wCTE
would work for some cases.  If strict mode doesn't affect EXECUTE (see
point 1 above), that could work too.  Or maybe there could be a new
command which runs a query, discards the results and ignores the number
of rows processed.

I'll be adding this to the open commitfest in hopes of getting some
feedback on this idea (I'm prepared to hear a lot of "you're crazy!"),
but feel free to comment away any time you please.


Regards,
Marko Tiikkaja

[1]: http://www.postgresql.org/message-id/510BF731.5020802@gmx.net