|
|
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();