Kevin Grittner wrote:
Tom Lane <t...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
