This patch set adds support for INOUT parameters to procedures. Currently, INOUT and OUT parameters are not supported.
A top-level CALL returns the output parameters as a result row. In PL/pgSQL, I have added special support to pass the output back into the variables, as one would expect.
These patches apply on top of the "prokind" patch set v2. (Tom has submitted an updated version of that, which overlaps with some of the changes I've made here. I will work on consolidating that soon.)
So ... no OUT parameters, though. I'm struggling to find a way to make this compatible with everything else. For functions, the OUT parameters don't appear in the signature. But that is not how this is specified in the SQL standard for procedures (I think). In PL/pgSQL, you'd expect that
CREATE PROCEDURE foo(a int, OUT b int) ...
could be called like
CALL foo(x, y);
but that would require a different way of parsing function invocation.
At the top-level, it's even more dubious. In DB2, apparently you write
CALL foo(123, ?);
with a literal ? for the OUT parameters.
In Oracle, I've seen CALL ... INTO syntax.
Anyway, I'm leaving this out for now. It can be worked around by using INOUT parameters. Future improvements would be mainly syntax/parsing adjustments; the guts that I'm implementing here would remain valid.
I am looking on attached code, and it looks pretty well. Can be really nice if this code will be part of release 11, because it is very interesting, important feature feature.
Regards
p.s. can be nice, if we allow same trick with calling of OUT variables functions in plpgsql
fx(in a, out x, out y) return int -- but requires some special mark
do $$
declare x int, y int, z int;
begin
z := fx(10, x, y);
raise notice '% ....
Then migration from Oracle can be really easy and friendly
Pavel
-- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
