From: | Darren Duncan <darren(at)darrenduncan(dot)net> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: returning multiple result sets from a stored procedure |
Date: | 2010-09-09 21:12:24 |
Message-ID: | 4C894DB8.9050205@darrenduncan.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-09-09 21:13:32 | Re: returning multiple result sets from a stored procedure |
Previous Message | Kevin Grittner | 2010-09-09 21:09:42 | Re: returning multiple result sets from a stored procedure |