Re: "stored procedures" - use cases?

On Mon, May 9, 2011 at 11:58 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> no - you are little bit confused :). CALL and function execution
> shares nothing. There is significant differences between function and
> procedure. Function is called only from executor - from some plan, and
> you have to know a structure of result before run. The execution of
> CALL is much simple - you just execute code - without plan and waiting
> for any result - if there is.

Now I'm a little confused, or you are. Surely any SQL has to be
planned and executed, regardless of whether it appears in a function,
a stored procedure, or anywhere else. Non-SQL statements within a
stored procedure don't need to go through the planner and executor,
but that's true in PL/python or PL/pgsql or whatever today.

I think people are using the term "stored procedures" to refer to
approximately whatever it is that they're unhappy that functions don't
allow, and that's leading to a lot of people talking across each
other. The main features seem to be (1) explicit transaction control
and/or execution of commands like VACUUM that can't be invoked from
within a transaction, (2) autonomous transactions, and (3) returning
multiple result sets. But I don't think anybody would be desperately
unhappy if it magically became possible to do those things from
regular functions, unlikely as that may seem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company