Re: returning multiple result sets from a stored procedure

Kevin Grittner wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> to my mind the main thing that would justify inventing a separate
>> PROCEDURE facility is if procedures were to execute outside the
>> transaction system, so that they could start and stop transactions
>> for themselves.
>  
> That is the biggest distinction in my mind, too.  Supporting
> multiple result sets just as if the queries were run as independent
> client-side statements would also be very important.  I have seen
> implementations which support, for a single stored procedure, OUT
> parameters, a RETURN value, and multiple result sets -- all at the
> same time, as separate things.  I haven't reviewed stored procedures
> in the SQL standard since an early draft proposal years ago, so I
> don't know what the current state of that is, but if PostgreSQL
> approaches this, it'd be nice to implement as many of the above as
> are not in conflict with requirements of the standard.

If it was reasonable I would go further in splitting and have at least 4 
distinct kinds of routines, here listed in order of invocablility (each routine 
kind can invoke anything above it on the list but not anything below it):

1.  Expression-invoked pure functions that only have IN parameters and can not 
directly see the database or have any side-effects and are always in a 
transaction.  Most operators are of this kind.

2.  Statement-invoked routines that are pure like #1 but also have OUT/INOUT 
parameters instead of resulting in a value like a function.  The assignment 
operator is of this kind.

3.  Routines that *can* see and update the database but are otherwise like #2, 
and are always in a transaction.  The general case of a SELECT or DML or DDL are 
of this kind.

4.  Routines that can cross transaction boundaries or control transactions but 
are otherwise like #2 or #3.  Transaction control statements are of this kind.

If I understand correctly, the existing Pg FUNCTION is essentially #3 and the 
proposed PROCEDURE is essentially #4.

Maybe I just have to RTFM but I don't know if it is possible now to declare a Pg 
FUNCTION that it stays in the restrictions of #1 or #2.  But if not, then I 
think it would be valuable to do so, for assisting reliability and performance.

-- Darren Duncan