Table of Contents
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();
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.
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();
When calling a function that returns
a refcursor you must cast the return type
of getObject to
a ResultSet
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();