Calling Stored Functions and Procedures

PostgreSQL® supports two types of stored objects, functions that can return a result value and - starting from v11 - procedures that can perform transaction control. Both types of stored objects are invoked using CallableStatement and the standard JDBC escape call syntax {call storedobject(?)} . The escapeSyntaxCallMode connection property controls how the driver transforms the call syntax to invoke functions or procedures.

The default mode, select , supports backwards compatibility for existing applications and supports function invocation only. This is required to invoke a function returning void.

For new applications, use escapeSyntaxCallMode=callIfNoReturn to map CallableStatements with return values to stored functions and CallableStatements without return values to stored procedures.

This example shows how to call the PostgreSQL® built-in function, upper, which simply converts the supplied string argument to uppercase.

CallableStatement upperFunc = conn.prepareCall("{? = call upper( ? ) }"); upperFunc.registerOutParameter(1, Types.VARCHAR); upperFunc.setString(2, "lowercase to uppercase"); upperFunc.execute(); String upperCased = upperFunc.getString(1); upperFunc.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.

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`

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.

// 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);  // Function call. CallableStatement func = conn.prepareCall("{? = call refcursorfunc() }"); func.registerOutParameter(1, Types.OTHER); func.execute(); ResultSet results = (ResultSet) func.getObject(1); while (results.next()) {  // do something with the results. } results.close(); func.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 .

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

This example shows how to call a PostgreSQL® procedure that uses transaction control.

// set up a connection String url = "jdbc:postgresql://localhost/test"; Properties props = new Properties(); ...other properties...  // Ensure EscapeSyntaxCallmode property set to support procedures if no return value  props.setProperty("escapeSyntaxCallMode", "callIfNoReturn"); Connection con = DriverManager.getConnection(url, props);  // Setup procedure to call. Statement stmt = con.createStatement(); stmt.execute("CREATE TEMP TABLE temp_val ( some_val bigint )"); stmt.execute("CREATE OR REPLACE PROCEDURE commitproc(a INOUT bigint) AS '" +  " BEGIN " +  " INSERT INTO temp_val values(a); " +  " COMMIT; " +  " END;' LANGUAGE plpgsql"); stmt.close();  // As of v11, we must be outside a transaction for procedures with transactions to work. con.setAutoCommit(true);  // Procedure call with transaction CallableStatement proc = con.prepareCall("{call commitproc( ? )}"); proc.setInt(1, 100); proc.execute(); proc.close();