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

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.

> 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');

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.

merlin