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

2016-04-10 18:49 GMT+02:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> 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.
>

This is the problem. Any other databases doesn't allow it - or it has
pretty different semantic (in T-SQL)

I am skeptical if benefit is higher than costs.

>
> 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.​
>

It is not valid (semantically) - you cannot throw result in pure SQL

>
> ​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.
>
>
>