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
