Re: Relax requirement for INTO with SELECT in pl/pgsql - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: Relax requirement for INTO with SELECT in pl/pgsql |
| Date | |
| Msg-id | CAKFQuwZ8dwgwRCMkZd6BM4vUWNhyNxWguG6CXdJ4LUfsYn6xZw@mail.gmail.com Whole thread Raw |
| In response to | Re: Relax requirement for INTO with SELECT in pl/pgsql (Pavel Stehule <pavel.stehule@gmail.com>) |
| Responses |
Re: Relax requirement for INTO with SELECT in pl/pgsql
(Pavel Stehule <pavel.stehule@gmail.com>)
|
| List | pgsql-hackers |
Hi2016-03-21 22:13 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:Hi2016-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.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-functionNow, 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 would be inclined to require that DML returning tuples requires INTO while a SELECT does not. Adding RETURNING is a deliberate user action that we can and probably should be conservative for. Writing SELECT is default user behavior and is quite often used only for its side-effects. Since SQL proper doesn't offer a means to distinguish between the two modes adding that distinction to pl/pgSQL, while useful, doesn't seem like something that has to be forced upon the user.
On the last point I probably wouldn't bother to deprecate PERFORM for that reason, let alone the fact we likely would never choose to actually remove the capability.
I'm not convinced that allowing RETURNING to be target-less is needed. With writable CTEs you can now get that capability by wrapping the DML in a target-less SELECT. Again, coming back to "typical usage", I'd have no problem making something like "RETURNING func(col) INTO /dev/null" work for the exceptional cases that need returning but don't have any good variables to assign the values to and don't want to make some up just to ignore them.
David J.
pgsql-hackers by date: