Re: Relax requirement for INTO with SELECT in pl/pgsql

2016-03-21 23:03 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Mon, Mar 21, 2016 at 4:13 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > Hi
> >
> > 2016-03-21 21:24 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> >>
> >> Patch is trivial (see below), discussion is not :-).
> >>
> >> I see no useful reason to require INTO when returning data with
> >> SELECT. However, requiring queries to indicate not needing data via
> >> PERFORM causes some annoyances:
> >>
> >> *) converting routines back and forth between pl/pgsql and pl/sql
> >> requires needless busywork and tends to cause errors to be thrown at
> >> runtime
> >>
> >> *) as much as possible, (keywords begin/end remain a problem),
> >> pl/pgsql should be a superset of sql
> >>
> >> *) it's much more likely to be burned by accidentally forgetting to
> >> swap in PERFORM than to accidentally leave in a statement with no
> >> actionable target. Even if you did so in the latter case, it stands
> >> to reason you'd accidentally leave in the target variable, too.
> >>
> >> *) the PERFORM requirement hails from the days when only statements
> >> starting with SELECT return data. There is no PERFORM equivalent for
> >> WITH/INSERT/DELETE/UPDATE and there are real world scenarios where you
> >> might have a RETURNING clause that does something but not necessarily
> >> want to place the result in a variable (for example passing to
> >> volatile function). Take a look at the errhint() clause below -- we
> >> don't even have a suggestion in that case.
> >>
> >> This has come up before, and there was a fair amount of sympathy for
> >> this argument albeit with some dissent -- notably Pavel. I'd like to
> >> get a hearing on the issue -- thanks. If we decide to move forward,
> >> this would effectively deprecate PERFORM and the documentation will be
> >> suitably modified as well.
> >
> >
> > My negative opinion is known. The PERFORM statement is much more
> workaround
> > than well designed statement, but I would to see ANSI/SQL based fix. I
> try
> > to compare benefits and loss.
>
> Well, pl/pgsql is based on oracle pl/sql so I don't see how the
> standard is involved. FWICT, "PERFORM" is a postgres extension to
> pl/pgsql. I don't see how the standard plays at all.
>

PERFORM is not interesting - it is proprietary extension.

>
> > Can you start with analyze what is possible, and what semantic is
> allowed in
> > standard and other well known SQL databases?
>
> Typical use of PERFORM is void returning function. Oracle allows use
> of those functions without any decoration at all. For example, in
> postgres we might do:
> PERFORM LogIt('I did something');
>
> in Oracle, you'd simply do:
> LogIt('I did something');
>

It is procedure call - it is not SELECT fx(), but CALL fx(), when CALL
statement is implicit.

>
> I'm not sure what Oracle does for SELECT statements without INTO/BULK
> UPDATE. I'm not really inclined to care -- I'm really curious to see
> an argument where usage of PERFORM actually helps in some meaningful
> way. Notably, SELECT without INTO is accepted syntax, but fails only
> after running the query. I think that's pretty much stupid but it's
> fair to say I'm not inventing syntax, only disabling the error.

> I'm not sure what other databases do is relevant. They use other
> procedure languages than pl//sql (the biggest players are pl/psm and
> t-sql) which have a different set of rules in terms of passing
> variables in and out of queries.
>

But this is important, and you are ignoring this case. If we allow "SELECT
expr;" when result will be ignored once, we cannot to revert it back ever.
So this can be really issue, when you will port applications between
Postgres and other, or if you will switch between Postgres and other db.

Regards

Pavel

>
> merlin
>