Re: PL/pgSQL 2

2014-09-02 14:16 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.
>