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

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

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
>