Re: INOUT parameters in procedures - Mailing list pgsql-hackers

2018-03-08 1:53 GMT+01:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.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.

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

Fixed. (memory context issue)

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.

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