Re: Multiple "selects" returned from a single stored procedure

Shachar Shemesh wrote:
> MS-SQL has the capacity for both out variables from stored procedures,
> as well as running several "selects" inside the procedures, and then
> giving the results for all selects to the caller. Fortunetly for me,
> that specific application doesn't run more than one select per stored
> procedure.
>
> The way I handled out variables so far was to have the function return a
> compound type, with the variables as rows. With embedded selects,
> however, this will no longer work.
>
> I guess what I would like to suggest is for the thus far unused "select"
> command in PLPGSQL to be used, in some way, to return values outside the
> scope of the strict "returns" context. I guess out variables will also
> be nice, but that's besides the point.

This has come up before (search the archives). I think the answer is to
implement actual stored procedures (as opposed to functions, which is
what we now have). A stored procedure call, per SQL99/2003 would look
something like:
call sp_my_stored_proc();
(which in MSSQL looks like "exec sp_my_stored_proc()")

The difference between this and an SRF is that the stored procedure
cannot be used in a FROM clause, and therefore cannot be joined with
other data or filtered with WHERE criteria. But that fact also means
that we should be able to deal with projecting multiple heterogenous
result sets, and the structure of the sets does not need to be known in
advance.

> If anyone has any ideas on how to both modify called parameters, and
> return a rowset, please let me know. Best I came up with so far was to
> create a temporary table for the out vars or the selects. I can then rig
> the OLE DB to make it look as if the function returned that.

I wonder if you could write an SRF that returns setof refcursor, and
then expand the cursors one-by-one in the OLE DB layer. See:
http://www.postgresql.org/docs/7.4/interactive/plpgsql-cursors.html
(37.8.3.3. Returning Cursors)

HTH,

Joe