On Sun, Apr 10, 2016 at 10:01 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote:
> > > 2016-04-10 18:49 GMT+02:00 David G. Johnston <david.g.johns...@gmail.com>: > >> On Sun, Apr 10, 2016 at 9:16 AM, Pavel Stehule <pavel.steh...@gmail.com> >> wrote: >> >>> >>> >>> 2016-04-10 17:49 GMT+02:00 David G. Johnston <david.g.johns...@gmail.com >>> >: >>> >>>> On Sun, Apr 10, 2016 at 1:13 AM, Pavel Stehule <pavel.steh...@gmail.com >>>> > wrote: >>>> >>>>> Hi >>>>> >>>>> 2016-03-21 22:13 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>: >>>>> >>>>>> Hi >>>>>> >>>>>> 2016-03-21 21:24 GMT+01:00 Merlin Moncure <mmonc...@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-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. >>>>> >>>>> >>>> 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. >>>> >>> >>> It doesn't help - SELECT is most often used construct. >>> >>> We can be less strict for SELECT expr, but SELECT FROM should not be >>> allowed without INTO clause. >>> >>> >> SELECT perform_this_action_for_every_user(user_id) FROM usertable; >> >> I still only care about side-effects. >> >> The rule remains (becomes?) simple: Use INTO if you need to capture the >> SQL value into a pl/pgSQL variable - otherwise don't. WRT your prior post >> I'd tell the user they are doing something really unusual if they write >> INSERT RETURNING without INTO - which I have no problem doing. >> > > This is the problem. Any other databases doesn't allow it - or it has > pretty different semantic (in T-SQL) > > I am skeptical if benefit is higher than costs. > This isn't T-SQL, and if you are not going to explain how it works and why its behavior is desirable I'm not going to be convinced that it matters. > > >> >> We don't need to force the user to tell us they intentionally omitted the >> INTO clause. The omission itself is sufficient. Using select without a >> target pl/pgSQL variable is a valid and probably quite common construct and >> hindering it because it might make debugging a function a bit harder (wrong >> data instead of an error) doesn't seem worthwhile. You are making >> accommodations for exceptional situations. I'm not convinced that it will >> be significantly harder to spot a missing INTO in a world where one is >> allowed to write such a statement without PERFORM. Yes, it will not be as >> convenient. Its a usability trade-off. >> >> There is value in having the non-procedural aspects of pl/pgSQL be as >> close to pure SQL as possible. >> > > It is not valid (semantically) - you cannot throw result in pure SQL > I have no idea what "throw result" means. David J.
