On 11/18/05, Guy Rouillier <guyr@xxxxxxxxxxx> 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 ;)
