On 09/02/2014 03:16 PM, Joel Jacobson wrote:
> 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/
In the mailing list thread that you linked there, Tom suggested using
"STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error
(http://www.postgresql.org/message-id/16397.1356106923@sss.pgh.pa.us).
What happened to that proposal?
- Heikki