3

Is there a technical reason why a JDBC CallableStatement shouldn't be used to execute a PostgreSQL stored function that returns a SETOF (in particular REFCURSOR)?

Per the PostgreSQL JDBC documentation:

Functions that return data as a set should not be called via the CallableStatement interface, but instead should use the normal Statement or PreparedStatement interfaces.

Beyond that, I haven't seen any reason, performance, bug related, or otherwise, why you shouldn't, and it does appear to work. It seems to inherently limit what you can do with certain functions (especially those returning multiple result sets), and a bit of an imposition when porting over procedures from another database to PostgreSQL.

I'm leery of ignoring a blatant warning from the organization releasing this software, but I am curious as to know more specifically why. I do realize that cursors and result sets are treated differently with respect to how much data is transmitted from the server to the client in a single shot, but this has already been documented as a shortcoming.

UPDATE A PostgreSQL implemented CallableStatement for a function converts the format of the call string into a select statement. Consequently, the JDBC execution of a function returning a SETOF (as well as all functions) is handled the same way as using a PreparedStatement.

A JDBC CallableStatement is a PreparedStatement that can handle output parameters. Because a PostgreSQL function returning a SETOF can't include output parameters, a CallableStatement doesn't provide any additional functionality for such functions. With that said, the PostgreSQL documentation doesn't discourage the use of a CallableStatement for functions that don't include output parameters, and there doesn't appear to be any technical reason at the moment as to why a CallableStatement shouldn't be used.

A colleague of mine pointed out that this warning has been in place for a long time, so it appears to be a design decision, rather than a bug. While a CallableStatement currently works (and I suspect will continue to work), there is no guarantee that it will continue to do so in the future, especially if this was a design decision.

HamburglarHelper
  • 31
  • 2
  • It looks like your most recent edit is more an attempt to answer your own question, consider posting it as an answer instead. – Mark Rotteveel Jul 20 '17 at 14:20
  • I did consider posting this as an answer, but ultimately it doesn't address the reason behind the warning, and that reason is a technical one. – HamburglarHelper Jul 20 '17 at 22:17

1 Answers1

0

If you try to use a CallableStatement to retrieve the result of a table function, you will only get the first result row.

So it does not cause an error, but the result may not be what you want. Hence the warning.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263