Re: Support for OUT parameters in procedures

po 28. 9. 2020 v 18:43 odesílatel Andrew Dunstan <
andrew(dot)dunstan(at)2ndquadrant(dot)com> napsal:

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

This was an important issue if I remember well. Passing mandatory NULL as
OUT arguments solves this issue.
I fully agree so OUT arguments are part of the procedure's signature.
Unfortunately, there is another difference
from functions, but I don't think so there is a better solution, and we
should live with it. I think it can work well.

>
> > 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 found one issue. The routine for selecting function or procedure based on
signature should be fixed.

CREATE OR REPLACE PROCEDURE public.procp(OUT integer)
LANGUAGE plpgsql
AS $procedure$
BEGIN
$1 := 10;
END;
$procedure$

DO
$$
DECLARE n numeric;
BEGIN
CALL procp(n);
RAISE NOTICE '%', n;
END;
$$;
ERROR: procedure procp(numeric) does not exist
LINE 1: CALL procp(n)
^
HINT: No procedure matches the given name and argument types. You might
need to add explicit type casts.
QUERY: CALL procp(n)
CONTEXT: PL/pgSQL function inline_code_block line 4 at CALL

I think this example should work.

But it doesn't work now for INOUT, and this fix will not be easy, so it
should be solved as a separate issue. This features are complete and useful
now, and it can be fixed later without problems with compatibility issues.

Another issue are using polymorphic arguments

postgres=# create or replace procedure px(anyelement, out anyelement)
as $$
begin
$2 := $1;
end;
$$ language plpgsql;

postgres=# call px(10, null);
ERROR: cannot display a value of type anyelement

but inside plpgsql it works
do $$
declare xx int;
begin
call px(10, xx);
raise notice '%', xx;
end;
$$;

> I think this can be marked RFC.
>

+1

Pavel

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