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

Shachar Shemesh wrote:
>> 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".

I think you're misinterpreting the docs. The CALL keyword has not been
implemented in Postgres AFAIK. Where precisely did you see that?

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

It is THE problem as far as implementing what you're looking for. An SRF
will never be able to deal with multiple results sets because the number
and types of the returned attributes must be known/resolved when the
query is parsed. On the other hand, a stored procedure would not have
this restriction if it, *by design could not* participate in a normal
SELECT.

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

Sure. I should think you'd allow a normal SELECT statement in your
stored procedure, exactly as you would do in MSSQL. The result tuples
could be formed and projected in similar fashion to EXPLAIN or SHOW ALL.
See, for example, ShowAllGUCConfig() in guc.c.

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

This seems cleanest, except you might want a configurable option to turn
it off, in case someone really wants the refcursor results.

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

This one seems a bit grotty to me. But the only other kludge I can think
of to similate output variables would require some backend hacking, or
at least a user defined C function.

In case you're interested, here is the idea. Implement functions
necessary to create, change, and remove session local variables. If the
function uses named parameters (recently implemented for functions)
*and* one or more session local variable of the same names are found,
assume the value of the variables are your outputvar results.

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

Cursors can now outlive transactions (DECLARE ... WITH HOLD), but there
is the downside (recently discussed on one of the lists) that when the
transaction is ended, the cursor is copied to a tuplestore. As long as
the tuplestore fits within sort_mem (work_mem in 7.5+), it will be held
entirely within memory. If not, it will spill to disk.

Joe