Re: returning multiple result sets from a stored procedure

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> to my mind the main thing that would justify inventing a separate
>> PROCEDURE facility is if procedures were to execute outside the
>> transaction system, so that they could start and stop transactions
>> for themselves.

> That is the biggest distinction in my mind, too. Supporting
> multiple result sets just as if the queries were run as independent
> client-side statements would also be very important. I have seen
> implementations which support, for a single stored procedure, OUT
> parameters, a RETURN value, and multiple result sets -- all at the
> same time, as separate things.

That seems rather overkill to me --- in particular, I don't understand
the point of a RETURN value when there can be no caller to return a
value to. Scalar OUT parameters could be sensible though; those could
be returned to the client as a one-row result set.

One point that has to be made is that returning multiple result sets
as if they were successive queries restricts the client to reading the
result sets serially; that is, you must read all of result A before you
can look at result B, etc. While that's frequently good enough, an
advantage of the return-some-cursors approach is that you can scan the
cursors in parallel. I'm not sure whether we need to provide that
flexibility in a procedure facility. One reason not to worry about it
is that you can't return a cursor if the procedure is outside any
transaction --- unless you make it a WITH HOLD cursor, which is mighty
expensive and should certainly not be the default behavior. It might
be sufficient to say that anyone needing that capability can return
names of WITH HOLD cursors as scalar OUT parameters, or use the existing
FUNCTION infrastructure.

regards, tom lane