2016-04-11 15:37 GMT+02:00 Merlin Moncure <mmonc...@gmail.com>: > On Sun, Apr 10, 2016 at 3: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 don't think it's a problem requiring people to use RETURN in order > to return data from the function. > > SQL functions BTW happily discard results and it's never been an issue > there FWICT. To address your other argument given below, there are > valid cases where you'd use RETURNING without having any interest in > capturing the set. For example, you might have a volatile function, > v_func() that does something and returns a value that may not be > essential to the caller (say, a count of rows adjusted). > > INSERT INTO foo ... > RETURNING v_func(foo.x); > > Scenarios (even if not very common) where dummy variables are required > and/or queries have to be written into more complex forms (say, into a > CTE) where you would not have to do so outside pl/pgsql greatly > outweigh your points that, 'users might do the wrong thing'. The > wrong thing is actually the right thing in some cases. > > Small aside here: One thing that t-sql did right and pl/sql did wrong > was to make the language a proper superset of sql. pl/pgsql's > hijacking INTO, BEGIN, END, and EXECUTE are really unfortunate as are > any behaviors that are incompatible with the regular language (like > requiring PERFORM); they fork the language and make building stored > procedures in pl/pgsql much more difficult if not impossible. I'm not > sure this is a really solvable problem, but at least it can be nibbled > at. > > What are the rules for pl/psm? >
SQL/PSM knows only "select statement: single row" - subclause 12.5 - and it is reference to ANSI SQL foundation - subclause 14.7 - where is defined SELECT INTO. INTO is mandatory. No other SELECT form is possible. This is defined in ANSI SQL 2011 - I have not access to more current drafts. I read a Oracle doc - there INTO or BULK COLLECT clauses are required. Regards Pavel > > merlin >
