Chapter 6. Calling Stored Functions

Table of Contents

Obtaining a ResultSet from a stored function
From a Function Returing SETOF type
From a Function Returing 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");
String upperCased = upperProc.getString(1);

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.

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

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.