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

On Sun, Apr 10, 2016 at 3: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 don't think it's a problem requiring people to use RETURN in order
to return data from the function.

SQL functions BTW happily discard results and it's never been an issue
there FWICT. To address your other argument given below, there are
valid cases where you'd use RETURNING without having any interest in
capturing the set. For example, you might have a volatile function,
v_func() that does something and returns a value that may not be
essential to the caller (say, a count of rows adjusted).

INSERT INTO foo ...
RETURNING v_func(foo.x);

Scenarios (even if not very common) where dummy variables are required
and/or queries have to be written into more complex forms (say, into a
CTE) where you would not have to do so outside pl/pgsql greatly
outweigh your points that, 'users might do the wrong thing'. The
wrong thing is actually the right thing in some cases.

Small aside here: One thing that t-sql did right and pl/sql did wrong
was to make the language a proper superset of sql. pl/pgsql's
hijacking INTO, BEGIN, END, and EXECUTE are really unfortunate as are
any behaviors that are incompatible with the regular language (like
requiring PERFORM); they fork the language and make building stored
procedures in pl/pgsql much more difficult if not impossible. I'm not
sure this is a really solvable problem, but at least it can be nibbled
at.

What are the rules for pl/psm?

merlin