On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Joel Jacobson <joel@trustly.com> wrote:
>
>> Sorry for being unclear, I didn't mean to suggest the main concern is
>> updating *all* rows.
>> The main concern is when you have a rather complex UPDATE WHERE clause,
>> aiming to update exactly one row. Some of the expressions might be
>> assertions, to just double-verify the values and to make it stand-out
>> you are checking those expressions.
>
>
> These are two different problems which probably need two different
> solutions. Making the default behavior of a set-based command that
> it throw an error if the resulting set is not exactly one row
> doesn't seem like the right solution to either one of them.
I see your point.
Basically, we have two types of applications where PL/pgSQL is commonly used.
a) OLTP applications where you typically operate on one row for each
UPDATE command.
b) Data warehouseing applications where you process multiple rows in
each UPDATE command.
Both have different desired default behaviours of the different
set-based commands used in PL/pgSQL.
I think both are important enough to motivate a nice syntax for both use-cases.
If we cannot change the default behaviour of UPDATE, then I vote for
the eariler proposed STRICT UPDATE syntax.
That would not protect novice users (like myself a couple of years
ago) who falsly thinks an UPDATE which updated 0 rows would fail.
But at least it would provide them a quite nice syntax to fix that
when shit hits the fan due to their failure.