Re: Support for OUT parameters in procedures

On 8/27/20 4:34 AM, Peter Eisentraut wrote:
> Procedures currently don't allow OUT parameters.  The reason for this
> is that at the time procedures were added (PG11), some of the details
> of how this should work were unclear and the issue was postponed.  I
> am now intending to resolve this.
>
> AFAICT, OUT parameters in _functions_ are not allowed per the SQL
> standard, so whatever PostgreSQL is doing there at the moment is
> mostly our own invention.  By contrast, I am here intending to make
> OUT parameters in procedures work per SQL standard and be compatible
> with the likes of PL/SQL.
>
> The main difference is that for procedures, OUT parameters are part of
> the signature and need to be specified as part of the call.  This
> makes sense for nested calls in PL/pgSQL like this:
>
> CREATE PROCEDURE test_proc(IN a int, OUT b int)
> LANGUAGE plpgsql
> AS $$
> BEGIN
>   b := a * 2;
> END;
> $$;
>
> DO $$
> DECLARE _a int; _b int;
> BEGIN
>   _a := 10;
>   CALL test_proc(_a, _b);
>   RAISE NOTICE '_a: %, _b: %', _a, _b;
> END
> $$;
>
> For a top-level direct call, you can pass whatever you want, since all
> OUT parameters are presented as initially NULL to the procedure code.
> So you could just pass NULL, as in CALL test_proc(5, NULL).
>
> The code changes to make this happen are not as significant as I had
> initially feared.  Most of the patch is expanded documentation and
> additional tests.  In some cases, I changed the terminology from
> "input parameters" to "signature parameters" to make the difference
> clearer. Overall, while this introduces some additional conceptual
> complexity, the way it works is pretty obvious in the end, and people
> porting from other systems will find it working as expected.
>

I've reviewed this, and I think it's basically fine. I've made an
addition that adds a test module that shows how this can be called from
libpq - that should be helpful (I hope) for driver writers.

A combined patch with the original plus my test suite is attached.

I think this can be marked RFC.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services