Обсуждение: Why CALL/PERFORM not part of core SQL?
Short version of story: I'm converting some Java->Oracle code to PG. It uses the standard JDBC batch facility, which is simply a collection of statements sent to the server as a group. Because batches are executed as a group, the individual statements in them are forbidden from returning values. The application is using batches of CALL statements to stored procedures, which works fine with Oracle, since stored procs there do not return values. The closest approximation in PG is to use SELECT on stored functions. You can see where this is going: SELECT returns a value (a JDBC ResultSet), so the code is bombing out with error "org.postgresql.util.PSQLException: A result was returned when none was expected." The really embarrassing thing is that I discovered this same problem 6 months ago and forgot about it; searching the JDBC list I found my own posting! Before I go back on JDBC to continue this discussion, I wanted to see if there is a specific reason why CALL or PERFORM is not made part of the core PG SQL implementation, as opposed to only being defined in pl/pgsql. I would think it might come in handy to other pl's. The alternative for Java, I suppose, is to allow these verbs and then translate them to SELECT in the driver and throw away any return value. That seems like more of a hack than a solution. I suppose the same could be said with respect to doing the same thing in the core language. -- Guy Rouillier
On 11/18/05, Guy Rouillier <guyr@masergy.com> wrote: > Short version of story: I'm converting some Java->Oracle code to PG. It > uses the standard JDBC batch facility, which is simply a collection of > statements sent to the server as a group. Because batches are executed > as a group, the individual statements in them are forbidden from > returning values. > > The application is using batches of CALL statements to stored > procedures, which works fine with Oracle, since stored procs there do > not return values. The closest approximation in PG is to use SELECT on > stored functions. You can see where this is going: SELECT returns a > value (a JDBC ResultSet), so the code is bombing out with error > "org.postgresql.util.PSQLException: A result was returned when none was > expected." The really embarrassing thing is that I discovered this same > problem 6 months ago and forgot about it; searching the JDBC list I > found my own posting! > > Before I go back on JDBC to continue this discussion, I wanted to see if > there is a specific reason why CALL or PERFORM is not made part of the > core PG SQL implementation, as opposed to only being defined in > pl/pgsql. I would think it might come in handy to other pl's. The > alternative for Java, I suppose, is to allow these verbs and then > translate them to SELECT in the driver and throw away any return value. > That seems like more of a hack than a solution. I suppose the same > could be said with respect to doing the same thing in the core language. > > -- > Guy Rouillier > > declare your functions as RETURNING void CREATE FUNCTION yourfunction RETURNS void AS $$... and do "select yourfunction();" -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
Jaime Casanova wrote: > On 11/18/05, Guy Rouillier <guyr@masergy.com> wrote: >> Short version of story: I'm converting some Java->Oracle code to PG. >> It uses the standard JDBC batch facility, which is simply a >> collection of statements sent to the server as a group. Because >> batches are executed as a group, the individual statements in them >> are forbidden from returning values. >> >> The application is using batches of CALL statements to stored >> procedures, which works fine with Oracle, since stored procs there do >> not return values. The closest approximation in PG is to use SELECT >> on stored functions. You can see where this is going: SELECT returns >> a value (a JDBC ResultSet), so the code is bombing out with error >> "org.postgresql.util.PSQLException: A result was returned when none >> was expected." The really embarrassing thing is that I discovered >> this same problem 6 months ago and forgot about it; searching the >> JDBC list I found my own posting! >> >> Before I go back on JDBC to continue this discussion, I wanted to see >> if there is a specific reason why CALL or PERFORM is not made part of >> the core PG SQL implementation, as opposed to only being defined in >> pl/pgsql. I would think it might come in handy to other pl's. The >> alternative for Java, I suppose, is to allow these verbs and then >> translate them to SELECT in the driver and throw away any return >> value. That seems like more of a hack than a solution. I suppose the >> same could be said with respect to doing the same thing in the core >> language. >> >> -- >> Guy Rouillier >> >> > > declare your functions as RETURNING void > > CREATE FUNCTION yourfunction RETURNS void AS $$... > > > and do "select yourfunction();" Thanks, but I'm already doing that. Because the SQL statement is a SELECT, it's still generating a ResultSet. The ResultSet happens to be empty, but that is immaterial. -- Guy Rouillier