Chapter 6. Calling Stored Functions

Table of Contents

• Obtaining a ResultSet from a stored function
  • From a Function Returning SETOF type
  • From a Function Returning a refcursor

Example 6.1. 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.

CallableStatement upperProc = conn.prepareCall("{ ? = call upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
String upperCased = upperProc.getString(1);
upperProc.close();

Obtaining a ResultSet from a stored function

PostgreSQL's™ stored functions can return results in two different ways. The function may return either a refcursor value or a SETOF some datatype. Depending on which of these return methods are used determines how the function should be called.

From a Function Returning SETOF type

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.

Example 6.2. Getting SETOF type values from a function

Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION setoffunc() RETURNS SETOF int AS "
     + "' SELECT 1 UNION SELECT 2;' LANGUAGE sql");
ResultSet rs = stmt.executeQuery("SELECT * FROM setoffunc()");
while (rs.next())
{
   // do something
}
rs.close();
stmt.close();

From a Function Returning a refcursor

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

Note

One notable limitation of the current support for a ResultSet created from a refcursor is that even though it is a cursor backed ResultSet, all data will be retrieved and cached on the client. The Statement fetch size parameter described in the section called “Getting results based on a cursor” is ignored. This limitation is a deficiency of the JDBC driver, not the server, and it is technically possible to remove it, we just haven't found the time.

Example 6.3. Getting refcursor Value From a Function

// Setup function to call.
Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '"
     + " DECLARE "
     + " mycurs refcursor; "
     + " BEGIN "
     + " OPEN mycurs FOR SELECT 1 UNION SELECT 2; "
     + " RETURN mycurs; "
     + " END;' language plpgsql");
stmt.close();

// We must be inside a transaction for cursors to work.
conn.setAutoCommit(false);

// Procedure call.
CallableStatement proc = conn.prepareCall("{ ? = call refcursorfunc() }");
proc.registerOutParameter(1, Types.OTHER);
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 cursor name directly. To do this, use the getString of ResultSet. With the underlying cursor name, you are free to directly use cursor commands on it, such as FETCH and MOVE.

Example 6.4. Treating refcursor as a cursor name

conn.setAutoCommit(false);
CallableStatement proc = conn.prepareCall("{ ? = call refcursorfunc() }");
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
String cursorName = proc.getString(1);
proc.close();