Re: Relax requirement for INTO with SELECT in pl/pgsql

2016-04-11 15:37 GMT+02:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Sun, Apr 10, 2016 at 3:13 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >
> > Hi
> >
> > 2016-03-21 22:13 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> >>
> >> Hi
> >>
> >> 2016-03-21 21:24 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)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
>