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

Joe Conway wrote:

> Shachar Shemesh wrote:
>
>> 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 did. Found people who asked about it, but didn't find an actual answer.

> 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 current docs say, at least from within pgplsql, that "call foo" 
translates to "select * from foo". psql doesn't seem to carry a "call" 
command at all. From PgOleDb, I just do "select * from foo".

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

I don't see that as a problem.

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

That would require some way of actually returning the results, wouldn't it?

>> 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.
>
> If anyone has any ideas on how to both modify called parameters, and
>
> 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.

Yes, that seems to be the direction to go. THANKS!

I'll see how easy it will be to implement in OLE DB, but returning setof 
refcursor certainly allows me to return several rowsets with different 
column info.

I have several ways I can take this solution, and I would like your opinion:
1. Simply assume that if a command returned a single column of 
refcursors, that it meant to return several rowsets, and implement the 
corresponding OLE DB interface.
2. Require that the cursors be named a certain way, according to their 
intended usage. If the command returned cursors named "<unnamed portal 
9>", to just treat it as is, while if it returned a cursor named 
"MultiResult1", treat it as above?

The advantage of 2 is that it allows me to simulate out variables. If 
the cursor is called "outputvars", I direct it to the output variables 
interface.
The disadvantage is that I'm not sure what to do if only some of the 
rows in the result are named MultiResult.

Whatever method I'll use, I may have to start a transaction for the 
purpose of the command, if we were not already in one. Otherwise, the 
ref-cursors are just useless strings.

> HTH,
>
> Joe
>
Thanks,
Shachar

-- 
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/