Re: returning multiple result sets from a stored procedure

Kevin Grittner wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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