Re: SQL-Invoked Procedures for 8.1

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.)

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

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.

regards, tom lane