Re: Support for OUT parameters in procedures - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Support for OUT parameters in procedures |
| Date | |
| Msg-id | CAFj8pRCTiFr2wPQHtiz5G6OC-x5hstKEjvWZ+Uu7wZ4CWV9oTg@mail.gmail.com Whole thread Raw |
| In response to | Re: Support for OUT parameters in procedures (Andrew Dunstan <andrew.dunstan@2ndquadrant.com>) |
| Responses |
Re: Support for OUT parameters in procedures
|
| List | pgsql-hackers |
po 28. 9. 2020 v 18:43 odesílatel Andrew Dunstan <andrew.dunstan@2ndquadrant.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$
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
$$
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;
as $$
begin
$2 := $1;
end;
$$ language plpgsql;
postgres=# call px(10, null);
ERROR: cannot display a value of type anyelement
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;
$$;
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
pgsql-hackers by date: