Re: Support for OUT parameters in procedures

On 2020-08-27 15:56, Robert Haas wrote:
> On Thu, Aug 27, 2020 at 4:34 AM Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>> 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).
>
> Is that actually how other systems work? I would think that people
> would expect to pass, say, a package variable, and expect that it will
> get updated.

The handling of results of SQL statements executed at the top level
(a.k.a. direct SQL) is implementation-specific and varies widely in
practice. More interesting in practice, in terms of functionality and
also compatibility, are nested calls in PL/pgSQL as well as integration
in JDBC.

We already support INOUT parameters in procedures, so the method of
returning the value of output parameters after the CALL already exists.
This patch doesn't touch that at all, really. If we had or would add
other places to put those results, such as package variables, then they
could be added independently of this patch.

Of course, feedback from those more knowledgeable in other systems than
me would be welcome.

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