Re: INOUT parameters in procedures

Hi

2018-03-08 1:53 GMT+01:00 Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com
>:

> On 3/6/18 04:22, Pavel Stehule wrote:
> > why just OUT variables are disallowed?
> >
> > The oracle initializes these values to NULL - we can do same?
>
> The problem is function call resolution. If we see a call like
>
> CALL foo(a, b, c);
>
> the this could be foo() with zero input and three output parameters, or
> with one input parameter and two output parameters, etc. We have no
> code to deal with that right now.
>

It looks like some error in this concept. The rules for enabling
overwriting procedures should modified, so this collision should not be
done.

When I using procedure from PL/pgSQL, then it is clear, so I place on *OUT
position variables. But when I call procedure from top, then I'll pass fake
parameters to get some result.

CREATE OR REPLACE PROCEDURE proc(IN a, OUT x, OUT y)
AS $$
BEGIN
x := a * 10;
y := a + 10;
END;
$$ LANGUAGE plpgsql;

CALL proc(10) -- has sense

but because just OUT variables are not possible, then the definition must
be changed to CREATE OR REPLACE PROCEDURE proc(IN a, INOUT x, INOUT y)

and CALL proc(10, NULL, NULL) -- looks little bit scarry

I understand so this is not easy solution (and it can be topic for other
releases), but I am thinking so it is solvable - but needs deeper change in
part, where is a routine is selected on signature. Now, this algorithm
doesn't calculate with OUT params.

This enhancing can be interesting for some purposes (and again it can helps
with migration from Oracle - although these techniques are usually used
inside system libraries):

a) taking more info from proc when it is required

PROCEDURE foo(a int);
PROCEDURE foo(a int, OUT detail text)

b) possible to directly specify expected result type

PROCEDURE from_json(a json, OUT int);
PROCEDURE from_json(a json, OUT date);
PROCEDURE from_json(a json, OUT text);

It is clear, so in environments when variables are not available, these
procedures cannot be called doe possible ambiguity.

This point can be closed now, I accept technical limits.

>
> > 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.
>
> Added a hint.
>

ok

>
> > I wrote recursive procedure. The call finished by exception. Why?
>
> Fixed. (memory context issue)
>

tested, it is ok now

>
> I added your example as a test case.
>
> > 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;
>
> Function resolution doesn't happen at compile time. That would require
> significant work in PL/pgSQL (possible perhaps, but major work). Right
> now, we do parse analysis at first execution.
>

ok, understand

looks well

all test passed,
code is well commented,
there are tests

if (argmodes && (argmodes[i] == PROARGMODE_INOUT ||
argmodes[i] == PROARGMODE_OUT))
+ {
+ Param *param;

Because PROARGMODE_OUT are disallowed, then this check is little bit messy.
Please, add some comment.

Regards

Pavel

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