2014-09-02 14:16 GMT+02:00 Joel Jacobson <j...@trustly.com>: > 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. >
It is main problem for me. I am thinking so verbosity is important. If it is ugly, cannot to say. It is subjective. > 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. > It is valid only for UPDATE, not for DELETE. You can delete with FK and it is common operation. > 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/ > It is way how to do COBOL from plpgsql. I am against it. Start to develop new language what will support fast development, but it is wrong way for plpgsql - and It is out my interest > > 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. >
