On 09/02/2014 03:16 PM, Joel Jacobson wrote:
On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule <pavel.steh...@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.1356106...@sss.pgh.pa.us).
What happened to that proposal?
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
