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