Re: INOUT parameters in procedures

Hi

2018-02-28 23:28 GMT+01:00 Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com>:

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