I'm pretty close to agreement with Josh, I think. Josh Berkus <j...@agliodbs.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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
