Re: Stored procedures and out parameters

On Tue, Aug 28, 2018 at 6:30 AM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> 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.

The semantics you've chosen for procedures are more like Oracle that
the existing function semantics, which, as I can attest from my work
experience, can be very useful for users looking to migrate. Worth
noting, however, is Oracle also has those semantics for function
calls. So what you've ended up creating here is a situation where
procedures behave more or less like they do in Oracle and the SQL
standard, but functions behave the way they historically have in
PostgreSQL. That's kind of a weird incompatibility, and I think that
incompatibility is a significant part of what people are complaining
about.

In other words, being more like the SQL standard is probably good, but
breaking compatibility is bad. You've technically avoided a
*backward* compatibility break by deciding that functions and
procedures can work differently from each other, but that just moves
the problem around. Now instead of being unhappy that existing code
is broken, people are unhappy that the new thing doesn't work like the
existing thing. That may be the lesser of evils, but it's still
pretty evil. People are not being unreasonable to want to call some
code stored on the server without having to worry about whether that
code is in a box labelled PROCEDURE or a box labelled FUNCTION.

This probably should have been discussed in more detail before this
got committed, but I guess that's water under the bridge at this
point. Nevertheless, I predict that this is going to be an ongoing
source of pain for a long time to come.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company