Re: [HACKERS] SQL procedures

2017-10-31 18:23 GMT+01:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>:

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.)

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers