The PostgreSQL JDBC Interface | ||
---|---|---|
<<< Previous | Next >>> |
Example 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(); |
ResultSet
from a stored functionPostgreSQL'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 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
|
Example 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.
<<< Previous | Home | Next >>> |
Creating and Modifying Database Objects | Storing Binary Data |