SQL procedures

From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: SQL procedures
Date: 2017-10-31 17:23:03
Message-ID: 336f6926-8990-ff2d-4167-276ce417fc32@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-SQL-procedures.patch text/plain 185.3 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2017-10-31 17:37:30 Re: Re: PANIC: invalid index offnum: 186 when processing BRIN indexes in VACUUM
Previous Message Michael Paquier 2017-10-31 17:01:27 Re: Remove secondary checkpoint