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

From Merlin Moncure
Subject Relax requirement for INTO with SELECT in pl/pgsql
Date
Msg-id CAHyXU0zYbeT-FzuonaaycbS9Wd8d5JO+_niAygzYtv5FMdx4rg@mail.gmail.com
Whole thread Raw
Responses Re: Relax requirement for INTO with SELECT in pl/pgsql
List pgsql-hackers
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.

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)



pgsql-hackers by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: snapshot too old, configured by time
Next
From: Pavel Stehule
Date:
Subject: Re: Relax requirement for INTO with SELECT in pl/pgsql