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.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services