Re: Stored procedures and out parameters

On 22/08/2018 20:22, Dave Cramer wrote:
> I also agree with David that driver writers made the best out of the
> situation with functions and we are now asking for the server to dual
> purpose the call command.
>
> Is there a technical reason why this is not possible ?

There are several areas of concern. These might not be grave issues
now, but they would impede future development in these areas.

First of all, what do you want to do with the function return value
when you use CALL? CALL doesn't have the capability to process
arbitrary shapes of return values, such as sets. It could perhaps be
implemented, but it's not. So right now, CALL could not be a general
replacement for all function invocations.

And would you expect a function that is invoked via CALL to have a
non-atomic execution context, that is, allow transactions? If not,
why not? If yes, how would this interact with set returning
functions? I don't think the implementation can support this.

Similar questions arise if we implement SQL standard dynamic result
sets. What would you do if a function invoked by CALL runs across one
of those?

Output parameter handling is not compatible between function calls and
procedure calls. Our implementation of output parameters in functions
is an extension of the SQL standard, and while it's been useful, it's
nonstandard, and I would like to make the output parameter handling in
CALL compatible with the SQL standard. For example, if you have a
function f1(IN a int, OUT b int), you would call it as SELECT f1(x)
and the "b" would somehow be the return value. But a procedure call
would be CALL p1(x, y), where x and y could be, say, PL/pgSQL
variables. So if you want to allow invoking functions using the CALL
statement, you're going to have a hard time defining semantics that
are not wildly confusing. Moreover, if the intention is to switch the
JDBC driver or similar drivers to use the CALL command always from
PG11 on, then the meaning of {call f1(a, b)} will have changed and a
lot of things will break in dangerous ways.

Always using CALL to invoke a function would also leave performance on
the table. CALL has to do certain additional work in case a
transaction commit happens in the middle of the procedure, such as
expanding TOAST values. You don't necessarily want to do that if you
don't have to.

There is also the semi-open question of whether functions and
procedures should be in separate namespaces. For PostgreSQL 11 we
have settled that they are in the same namespace, for simplicity and
because we ran out of time, but that choice should perhaps not be set
in stone for all times. In Oracle and DB2, functions and procedures
are in different namespaces, so SELECT x() and CALL x() invoke
different objects. Whether we ever want to do that is a different
question, but we shouldn't design ourselves into an incompatible
corner in haste.

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