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

On Sun, Apr 10, 2016 at 9:16 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2016-04-10 17:49 GMT+02:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:
>
>> On Sun, Apr 10, 2016 at 1:13 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>>> Hi
>>>
>>> 2016-03-21 22:13 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>
>>>> 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.
>>>>>
>>>>
>>>>
>>> here is another argument why this idea is not good.
>>>
>>>
>>> http://stackoverflow.com/questions/36509511/error-query-has-no-destination-for-result-data-when-writing-pl-pgsql-function
>>>
>>> Now, when people coming from T-SQL world use some T-SQL constructs, then
>>> usually the code should not work with the error "query has not destination
>>> for data ... "
>>>
>>> When PLpgSQL will be more tolerant, then their code will be executed
>>> without any error, but will not work.
>>>
>>>
>> I would be inclined to require that DML returning tuples requires INTO
>> while a SELECT does not. Adding RETURNING is a deliberate user action that
>> we can and probably should be conservative for. Writing SELECT is default
>> user behavior and is quite often used only for its side-effects. Since SQL
>> proper doesn't offer a means to distinguish between the two modes adding
>> that distinction to pl/pgSQL, while useful, doesn't seem like something
>> that has to be forced upon the user.
>>
>
> It doesn't help - SELECT is most often used construct.
>
> We can be less strict for SELECT expr, but SELECT FROM should not be
> allowed without INTO clause.
>
>
​SELECT perform_this_action_for_every_user(user_id) FROM usertable;

I still only care about side-effects.

The rule remains (becomes?) simple: Use INTO if you need to capture the
SQL value into a pl/pgSQL variable - otherwise don't. WRT your prior post
I'd tell the user they are doing something really unusual if they write
INSERT RETURNING without INTO - which I have no problem doing.

We don't need to force the user to tell us they intentionally omitted the
INTO clause. The omission itself is sufficient. Using select without a
target pl/pgSQL variable is a valid and probably quite common construct and
hindering it because it might make debugging a function a bit harder (wrong
data instead of an error) doesn't seem worthwhile. You are making
accommodations for exceptional situations. I'm not convinced that it will
be significantly harder to spot a missing INTO in a world where one is
allowed to write such a statement without PERFORM. Yes, it will not be as
convenient. Its a usability trade-off.

​There is value in having the non-procedural aspects of pl/pgSQL be as
close to pure SQL as possible.​

​I am not in a position to realistically judge the trade-offs involved here
as it pertains to something learning the language. I personally haven't
found the need to specify PERFORM particularly problematic but I've also
never been bit by the inverse - specifying PERFORM when in fact I needed to
assign to a variable. I guess my main point is I see no fundamental reason
to require a user to explicitly inform that they are omitting the INTO
clause but don't see that changing the status-quo will affect a significant
change in my quality of life. My experiences are quite limited though and
I'd be more inclined to side with the thoughts of those who are interacting
with less experienced (and generally a wider variety) developers on a daily
basis.

David J.