Re: PL/pgSQL 2 - Mailing list pgsql-hackers

On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> What we can do better?
>
> 1. we can implement a conditional RAISE
>
> DELETE FROM tab WHERE xx = somevar;
> GET DIAGNOSTICS  rc = ROW_COUNT;
> RAISE EXCEPTION 'some' WHEN rc <> 0;
>
> It is relatively natural and we use similar construct in CONTINUE statement.
>
> 2. What can be next? We can implement some idiom (shortcut) for GET
> DIAGNOSTICS
>
> DELETE FROM tab WHERE xx = somevar;
> RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;
>
> 3. What next? Maybe some notations -
>
> -- ** ensure_exact_one_row
> DELETE FROM tab WHERE xx = somevar;
>
> But default will be same as in plain SQL.

All three suggestions are either too verbose, ugly or hackish.
I write too much code every day in PL/pgSQL to find any other solution
than the cleanest and simplest to be acceptable.
I reckon there are those who mostly use the language to create
aggregated reports or to run some kind of batch jobs.
But I use it almost exlusively for OLTP, and then you most often
update a single row, and if 0 or >1 rows are affected, it's an error.
Therefore, I wish the syntax for the most common use case to be as
clean as possible, and there is nothing cleaner than plain UPDATE.

Also, when showing a beginner the power of PL/pgSQL, it cannot be
acceptable to have to write two rows to do something as simple as an
update. All the suggestions above range between 2-3 rows (for DELETE,
but I guess the syntax would be the same for UPDATE).

For an in-depth discussion on this subject, please see
http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/

I have no good ideas though on what the syntax would look like to
allow zero rows or multiple rows for an UPDATE though.

It's much harder to come up with things to *add* to a syntax than what
obvious ugliness you want to *remove*.

If I had to guess though, I would think something in the end of the
UPDATE command like a new keyword, could work. It wouldn't mess up the
syntax too much, and wouldn't require an extra line of code.

I strongly feel we should give a plain UPDATE without any extra lines
of code or special syntax a default behaviour, which is different from
"accept any number of affected rows".
My definitive vote is to throw an error if not exactly 1 row was
affected, and to provide a nice syntax to allow the other use cases.
Right now it's the other way around, we never throw an error, and
*always* have to check how many rows were affected. That means we
*always* get both more lines of code and also uglier code in our
applications, than we would if we optimized for the most common use
case.