Re: "stored procedures"

On Mon, May 9, 2011 at 1:41 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Josh Berkus wrote:
>> Peter,
>>
>> > I would like to collect some specs on this feature.  So does anyone have
>> > links to documentation of existing implementations, or their own spec
>> > writeup?  A lot of people appear to have a very clear idea of this
>> > concept in their own head, so let's start collecting those.
>>
>> Delta between SPs and Functions for PostgreSQL:
>>
>> * SPs are executed using CALL or EXECUTE, and not SELECT.
>>
>> * SPs do not return a value
>> ** optional: SPs *may* have OUT parameters.
>
> [ Late reply.]
>
> What is it about stored procedures that would require it not to return a
> value or use CALL?  I am trying to understand what part of this is
> "procedures" (doesn't return a values, we decided there isn't much value
> for that syntax vs. functions), and anonymous transactions.

FWICT the sql standard. The only summary of standard behaviors I can
find outside of the standard itself is here:
http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html.
Peter's synopsis of how the standard works is murky at best and
competing implementations are all over the place...SQL server's
'CALL' feature is basically what I personally would like to see. It
would complement our functions nicely.

Procedures return values and are invoked with CALL. Functions return
values and are in-query callable.

The fact that 'CALL' is not allowed inside a query seems to make it
pretty darn convenient to make the additional distinction of allowing
transactional control statements there and not in functions. You
don't *have* to allow transactional control statements and could offer
this feature as an essentially syntax sugar enhancement, but then run
the risk of boxing yourself out of a useful properties of this feature
later on because of backwards compatibility issues (in particular, the
assumption that your are in a running transaction in the procedure
body).

merlin