Re: Relax requirement for INTO with SELECT in pl/pgsql - Mailing list pgsql-hackers

Hi

2016-03-21 21:24 GMT+01:00 Merlin Moncure <mmoncure@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers