Re: INOUT parameters in procedures

2018-03-05 19:38 GMT+01:00 Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com>:

> On 3/5/18 11:00, Pavel Stehule wrote:
> > I am looking on attached code, and it looks pretty well. Can be really
> > nice if this code will be part of release 11, because it is very
> > interesting, important feature feature.
>
> Here is an updated patch, rebased on top of several recent changes, also
> added more documentation and tests in other PLs.
>
>
why just OUT variables are disallowed?

The oracle initializes these values to NULL - we can do same?

Minimally this message is not too friendly, there should be hint - "only
INOUT is suported" - but better support OUT too - from TOP OUT variables
should not be passed. from PL should be required.

I wrote recursive procedure. The call finished by exception. Why?

create or replace procedure p(x int,inout a int, inout b numeric)
as $$
begin
raise notice 'xxx % %', a, b;
if (x > 1) then
a := x / 10;
b := x / 2;
call p(b::int, a, b);
end if;
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# call p(100, -1, -1);
NOTICE: xxx -1 -1
NOTICE: xxx 10 50
NOTICE: xxx 5 25
NOTICE: xxx 2 12
NOTICE: xxx 1 6
NOTICE: xxx 0 3
NOTICE: xxx 0 1
ERROR: unsupported target
CONTEXT: PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL
SQL statement "CALL p(b::int, a, b)"
PL/pgSQL function p(integer,integer,numeric) line 4 at CALL

Because these variables are INOUT then it should work.

This issue can be detected in compile time, maybe?

postgres=# create or replace procedure p(x int,inout a int, inout b numeric)
as $$
begin raise notice 'xxx % %', a, b;if (x > 1) then
a := x / 10;
b := x / 2; call p(b::int, a, 10); <--- can be detected in compile time?
end if;
end;
$$ language plpgsql;

Is terrible, how this patch is short.

Regards

Pavel

>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>