I've been working on SQL procedures. (Some might call them "stored procedures", but I'm not aware of any procedures that are not stored, so that's not a term that I'm using here.)
Everything that follows is intended to align with the SQL standard, at least in spirit.
This first patch does a bunch of preparation work. It adds the CREATE/ALTER/DROP PROCEDURE commands and the CALL statement to call a procedure. It also adds ROUTINE syntax which can refer to a function or procedure. I have extended that to include aggregates. And then there is a bunch of leg work, such as psql and pg_dump support. The documentation is a lot of copy-and-paste right now; that can be revisited sometime. The provided procedural languages (an ever more confusing term) each needed a small touch-up to handle pg_proc entries with prorettype == 0.
Right now, there is no support for returning values from procedures via OUT parameters. That will need some definitional pondering; and see also below for a possible alternative.
With this, you can write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent Oracle.
Separately, I will send patches that implement (the beginnings of) two separate features on top of this:
- Transaction control in procedure bodies
- Returning multiple result sets
(In various previous discussions on "real stored procedures" or something like that, most people seemed to have one of these two features in mind. I think that depends on what other SQL systems one has worked with previously.)
Not sure if disabling RETURN is good idea. I can imagine so optional returning something like int status can be good idea. Cheaper than raising a exception.
Regards
Pavel
-- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
