This page in other versions: Unsupported versions: 7.4

31.5. Calling Stored Functions

PostgreSQL's JDBC driver fully supports calling PostgreSQL stored functions.

Example 31-4. Calling a built in stored function

This example shows how to call a PostgreSQL built in function, upper, which simply converts the supplied string argument to uppercase.

// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
String upperCased = upperProc.getString(1);
upperProc.close();

31.5.1. Using the CallableStatement Interface

All the considerations that apply for Statement and PreparedStatement apply for CallableStatement but in addition you must also consider one extra restriction:

  • You can only call a stored function from within a transaction.

31.5.2. Obtaining ResultSet from a stored function

PostgreSQL's stored function can return results by means of a refcursor value. A refcursor.

As an extension to JDBC, the PostgreSQL JDBC driver can return refcursor values as ResultSet values.

Example 31-5. Getting refcursor values from a function

When calling a function that returns a refcursor you must cast the return type of getObject to a ResultSet

// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, -1);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
  // do something with the results...
}
results.close();
proc.close();

It is also possible to treat the refcursor return value as a distinct type in itself. The JDBC driver provides the org.postgresql.PGRefCursorResultSet class for this purpose.

Example 31-6. Treating refcursor as a distinct type

con.setAutoCommit(false);
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, 0);
org.postgresql.PGRefCursorResultSet refcurs 
    = (PGRefCursorResultSet) con.getObject(1);
String cursorName = refcurs.getRefCursor();
proc.close();

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | About PostgreSQL
Copyright © 1996-2016 The PostgreSQL Global Development Group