Re: Stored procedures and out parameters - Mailing list pgsql-hackers
From | Robert Haas |
---|---|
Subject | Re: Stored procedures and out parameters |
Date | |
Msg-id | CA+TgmoYCxvReZsvo4RAuzKSrFm71RuRuXDGcb3xPsc=0axdwvw@mail.gmail.com Whole thread Raw |
In response to | Re: Stored procedures and out parameters (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>) |
Responses |
Re: Stored procedures and out parameters
Re: Stored procedures and out parameters Re: Stored procedures and out parameters |
List | pgsql-hackers |
On Tue, Aug 28, 2018 at 6:30 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.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
pgsql-hackers by date: