Calling Stored Functions and Procedures

PostgreSQL® supports two types of stored objects, functions that can return a result value and - starting from v11 - proceduresthat can perform transaction control. Both types of stored objects are invoked usingCallableStatement and the standardJDBC escape call syntax{call storedobject(?)} . TheescapeSyntaxCallMode connection property controls how the drivertransforms the call syntax to invoke functions or procedures.

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

For new applications, useescapeSyntaxCallMode=callIfNoReturn to mapCallableStatements with return values to storedfunctions andCallableStatements without return values to stored procedures.

Example 6.1. Calling a built-in stored function

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

CallableStatementupperFunc=conn.prepareCall("{? = call upper( ? ) }");upperFunc.registerOutParameter(1,Types.VARCHAR);upperFunc.setString(2,"lowercase to uppercase");upperFunc.execute();StringupperCased=upperFunc.getString(1);upperFunc.close();

Obtaining aResultSet from a stored function

PostgreSQL’s™ stored functions can return results in two different ways. The function may return either a refcursor valueor aSETOF some datatype. Depending on which of these return methods are used determines how the function should be called.

From a Function ReturningSETOF type

Functions that return data as a set should not be called via theCallableStatement interface, but instead should usethe normalStatement orPreparedStatement interfaces.

Example 6.2. GettingSETOF type values from a function
Statementstmt=conn.createStatement();stmt.execute("CREATE OR REPLACE FUNCTION setoffunc() RETURNS SETOF int AS "+"' SELECT 1 UNION SELECT 2;' LANGUAGE sql");ResultSetrs=stmt.executeQuery("SELECT * FROM setoffunc()");while(rs.next()){// do something}rs.close();stmt.close();

From a Function Returning a refcursor

When calling a function that returns a refcursor you must cast the return type ofgetObject to a ResultSet`

NOTE

One notable limitation of the current support for aResultSet created from a refcursor is that even though it is acursor backedResultSet , all data will be retrieved and cached on the client. TheStatement fetch size parameterdescribed in the section calledGetting results based on a cursoris ignored. This limitation is a deficiency of the JDBC driver, not the server, and it is technically possible to removeit, we just haven’t found the time.

Example 6.3. Getting refcursor Value From a Function
// Setup function to call.Statementstmt=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.CallableStatementfunc=conn.prepareCall("{? = call refcursorfunc() }");func.registerOutParameter(1,Types.OTHER);func.execute();ResultSetresults=(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 thegetString ofResultSet . With the underlying cursor name,you are free to directly use cursor commands on it, such asFETCH andMOVE .

Example 6.4. Treating refcursor as a cursor name
conn.setAutoCommit(false);CallableStatementfunc=conn.prepareCall("{? = call refcursorfunc() }");func.registerOutParameter(1,Types.OTHER);func.execute();StringcursorName=func.getString(1);func.close();
Example 6.5. Calling a stored procedure

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

// set up a connectionStringurl="jdbc:postgresql://localhost/test";Propertiesprops=newProperties();...otherproperties...// Ensure EscapeSyntaxCallmode property set to support procedures if no return valueprops.setProperty("escapeSyntaxCallMode","callIfNoReturn");Connectioncon=DriverManager.getConnection(url,props);// Setup procedure to call.Statementstmt=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 transactionCallableStatementproc=con.prepareCall("{call commitproc( ? )}");proc.setInt(1,100);proc.execute();proc.close();
Issuing a Query and Processing the Result
Storing Binary Data
Calling Stored Functions and Procedures