Re: [HACKERS] SQL procedures

On 23 November 2017 at 03:47, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com
> wrote:

>
>
> On 11/22/2017 02:39 PM, Corey Huinker wrote:
> >
> >
> > T-SQL procedures returns data or OUT variables.
> >
> > I remember, it was very frustrating
> >
> > Maybe first result can be reserved for OUT variables, others for
> > multi result set
> >
> >
> > It's been many years, but if I recall correctly, T-SQL returns a
> > series of result sets, with no description of the result sets to be
> > returned, each of which must be consumed fully before the client can
> > move onto the next result set. Then and only then can the output
> > parameters be read. Which was very frustrating because the OUT
> > parameters seemed like a good place to put values for things like
> > "result set 1 has 205 rows" and "X was false so we omitted one result
> > set entirely" so you couldn't, y'know easily omit entire result sets.
> >
>
>
>
> Exactly. If we want to handle OUT params this way they really need to be
> the first resultset for just this reason. That could possibly be done by
> the glue code reserving a spot in the resultset list and filling it in
> at the end of the procedure.
>

I fail to understand how that can work though. Wouldn't we have to buffer
all the resultset contents on the server in tuplestores or similar, so we
can send the parameters and then the result sets?

Isn't that going to cause a whole different set of painful difficulties
instead?

What it comes down to is that if we want to see output params before result
sets, but the output params are only emitted when the proc returns, then
someone has to buffer. We get to choose if it's the client or the server.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services