JDBC CallableStatement PostgreSQL Stored Function

A JDBC CallableStatement example to show you how to call a stored function from PostgreSQL database.

P.S Tested with PostgreSQL 11 and Java 8

	<dependency>
		<groupId>org.postgresql</groupId>
		<artifactId>postgresql</artifactId>
		<version>42.2.5</version>
	</dependency>

1. Call Function

1.1 Create a stored function and calling it via JDBC.

package com.mkyong.jdbc.callablestatement;
import java.sql.*;
public class FunctionReturnString {
    public static void main(String[] args) {
        String createFunction = "CREATE OR REPLACE FUNCTION hello(p1 TEXT) RETURNS TEXT "
                + " AS $$ "
                + " BEGIN "
                + " RETURN 'hello ' || p1; "
                + " END; "
                + " $$ "
                + " LANGUAGE plpgsql";
        String runFunction = "{ ? = call hello( ? ) }";
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement();
             CallableStatement callableStatement = conn.prepareCall(runFunction)) {
            // create or replace stored function
            statement.execute(createFunction);
            //----------------------------------
            // output
            callableStatement.registerOutParameter(1, Types.VARCHAR);
            // input
            callableStatement.setString(2, "mkyong");
            // Run hello() function
            callableStatement.executeUpdate();
            // Get result
            String result = callableStatement.getString(1);
            System.out.println(result);
        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();

Output

hello mkyong

1.2 SQL version.

CREATE OR REPLACE FUNCTION hello(p1 TEXT) RETURNS TEXT 
AS $$
BEGIN
    RETURN 'hello ' || p1;
END;
$$
LANGUAGE plpgsql;
-- run it 
select hello('mkyong');
-- output: hello mkyong

2. Function Returning SETOF

2.1 For Functions that return data as a SETOF, we should use the normal Statement or PreparedStatement, NOT CallableStatement

P.S The table pg_roles is a system table containing database roles

package com.mkyong.jdbc.callablestatement;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class FunctionReturnResultSet {
    public static void main(String[] args) {
        List<String> users = new ArrayList<>();
        String createFunction = "CREATE OR REPLACE FUNCTION getRoles() RETURNS SETOF pg_roles "
                + " AS 'select * from pg_roles' LANGUAGE sql;";
        String runFunction = "select * from getRoles();";
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement()) {
            // create a function returns as SETOF
            statement.execute(createFunction);
            // run it
            ResultSet resultSet = statement.executeQuery(runFunction);
            while (resultSet.next()) {
                users.add(resultSet.getString("rolname"));
            System.out.println("Database roles...");
            users.forEach(x -> System.out.println(x));
        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();

Output

Database roles...
pg_signal_backend
pg_read_server_files
postgres
pg_write_server_files
pg_execute_server_program
pg_read_all_stats
pg_monitor
pg_read_all_settings
pg_stat_scan_tables

2.2 SQL version.

CREATE OR REPLACE FUNCTION getRoles() RETURNS SETOF pg_roles 
AS 'select * from pg_roles' LANGUAGE sql;
-- run it 
select * from getRoles();

3. Function Returning Cursor

3.1 JDBC + Ref Cursor example.

package com.mkyong.jdbc.callablestatement;
import java.sql.*;
public class FunctionReturnRefCursor {
    public static void main(String[] args) {
        String createFunction = "CREATE OR REPLACE FUNCTION getUsers(mycurs OUT refcursor) "
                + " RETURNS refcursor "
                + " AS $$ "
                + " BEGIN "
                + "     OPEN mycurs FOR select * from pg_user; "
                + " END; "
                + " $$ "
                + " LANGUAGE plpgsql";
        String runFunction = "{? = call getUsers()}";
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement();
             CallableStatement cs = conn.prepareCall(runFunction);
        ) {
            // We must be inside a transaction for cursors to work.
            conn.setAutoCommit(false);
            // create function
            statement.execute(createFunction);
            // register output
            cs.registerOutParameter(1, Types.REF_CURSOR);
            // run function
            cs.execute();
            // get refcursor and convert it to ResultSet
            ResultSet resultSet = (ResultSet) cs.getObject(1);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("usename"));
                System.out.println(resultSet.getString("passwd"));
        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();

Output – This database contains a single user for testing :)

postgres
********

3.2 SQL version.

CREATE OR REPLACE FUNCTION getUsers(mycurs OUT refcursor) RETURNS refcursor 
	AS $$
	BEGIN 
		OPEN mycurs FOR select * from pg_user;
	END;
	$$
	LANGUAGE plpgsql;