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

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

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

​This isn't T-SQL, and if you are not going to explain how it works and why
its behavior is desirable I'm not going to be convinced that it matters.

>
>
>>
>> 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 have no idea what "throw result" means.​

​David J.
​