Re: "stored procedures"

I'm pretty close to agreement with Josh, I think.

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Delta between SPs and Functions for PostgreSQL:
>
> * SPs are executed using CALL or EXECUTE, and not SELECT.

Agreed, although some products will search for a matching procedure
name if the start of a statement doesn't match any reserved word.
That can be handy -- you run them more or less like commands.

> * SPs do not return a value

I've used some products where these were available, although in some
cases only setting what in PostgreSQL would be the equivalent of an
integer session GUC.

> ** optional: SPs *may* have OUT parameters.

Support for those would be important to handle some common uses of
SPs.

> * SPs have internal transactions including begin/commit

Yeah. Entering or leaving an SP should not start or end a
transaction. BEGIN, COMMIT, ROLLBACK, and SAVEPOINT should all be
available and should not disrupt statement flow.

> ** optional: SPs can run non-transaction statements,
> like CREATE INDEX CONCURRENTLY and VACUUM

That seems important.

> ** corollary: SPs may not be called as part of a larger query

OK.

> ** question: if an SP is called by another SP, what is its
> transaction context?

Entering or leaving an SP should not start or end a transaction.

> * optional: SPs can return multisets (ala SQL Server).

I think that's important.

> ** question: how would multisets be handled on the client end?

In previous discussions there seemed to be a feeling that unless we
were going to go to a new major version of the protocol, the return
from an SP would be an array of result sets. We would probably want
to reserve the first one for OUT parameters (and if we decide to
support it, the return value). Tools like psql would need to
display each in its turn, similar to what we do for some backslash
commands.

-Kevin