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

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.

Regards

Pavel

> My negative opinion is known. The PERFORM statement is much more
> workaround than well designed statement, but I would to see ANSI/SQL based
> fix. I try to compare benefits and loss.
>
> Can you start with analyze what is possible, and what semantic is allowed
> in standard and other well known SQL databases?
>
> Regards
>
> Pavel
>
>
>>
>> merlin
>>
>>
>>
>> diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
>> index b7f44ca..a860066 100644
>> --- a/src/pl/plpgsql/src/pl_exec.c
>> +++ b/src/pl/plpgsql/src/pl_exec.c
>> @@ -3457,12 +3457,9 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
>> }
>> else
>> {
>> - /* If the statement returned a tuple table, complain */
>> + /* If the statement returned a tuple table, free it. */
>> if (SPI_tuptable != NULL)
>> - ereport(ERROR,
>> - (errcode(ERRCODE_SYNTAX_ERROR),
>> - errmsg("query has no destination for result data"),
>> - (rc == SPI_OK_SELECT) ? errhint("If you want to
>> discard the results of a SELECT, use PERFORM instead.") : 0));
>> + SPI_freetuptable(SPI_tuptable);
>> }
>>
>> if (paramLI)
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>