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

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.

It is reason, why I dislike this proposal, because the rules when INTO is
allowed, required will be more complex. Now, the rules are pretty simple -
and it is safe for beginners. I accept so current behave should be limiting
for experts.

Regards

Pavel

>
> On the last point I probably wouldn't bother to deprecate PERFORM for that
> reason, let alone the fact we likely would never choose to actually remove
> the capability.
>
> ​I'm not convinced that allowing RETURNING to be target-less is needed.
> With writable CTEs you can now get that capability by wrapping the DML in a
> target-less SELECT. Again, coming back to "typical usage", I'd have no
> problem making something like "RETURNING func(col) INTO /dev/null" ​work
> for the exceptional cases that need returning but don't have any good
> variables to assign the values to and don't want to make some up just to
> ignore them.
>
> David J.
>
>