Re: SQL-Invoked Procedures for 8.1

On Sat, 2 Oct 2004, Tom Lane wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> >> I concur with Grant Finnemore's objection as well: people expect
> >> procedures to be able to return resultsets, ie SETOF something,
> >> not only scalar values. Whether this is what SQL2003 says is not
> >> really the issue -- we have to look at what's out there in competing
> >> products.
>
> > Agreed. Any suggestions about how a SETOF OUT parameter would be accessed
> > by the client? We have a variety of options: returning the results as if
> > it was a normal SELECT; returning some kind of delimited string and
> > providing an API to scroll it on the client side? There's got to be
> > something better than that :-).
>
> For the case of a single OUT SETOF parameter, acting as though the CALL
> were a SELECT would work nicely. The hard part is what to do if there
> are multiple such parameters. We could possibly return them as
> successive SELECT results but this would break a whole lot of pretty
> fundamental things at both the protocol and client-library-API level.
> (The old protocol actually could handle it, but the V3 protocol is
> going to have problems.)

Yes, that's what I suspected.

>
> How do you feel about restricting SPs to have at most one SETOF result?

I think its a restriction we could do without and others seem to be
suggesting that we at least need an analogous feature so that people can
have SPs return multiple result sets.

>
> Plan B would be to implement each SETOF result as if it were a cursor.
> Say, the system would pass back a cursor (portal) name in the same
> way as a scalar OUT result would be returned, and the client would need
> to do FETCH operations to pull the actual rows. I'm not sure what to
> say about the lifespan of such cursors --- ordinary cursors go away at
> transaction end, but if an SP is invoked outside of the transaction
> system then this isn't going to do for SP results.

Why not go the whole way and just have a cursor type for these kind of
parameters? I'd imagine that this would also allow users an opaque result
set. That is, the columns of the result set could be determined at run
time.

Gavin