call a stored function on postgres from java

Provided you understand how to use JDBC to talk to postgres (if not this tutorial should get you up to speed), then this JDBC page shows how to call stored functions:

For example:

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

There are other kinds of stored function discussed on the latter link also.

Calling postgresql function/stored procedure is little bit different than normal Sql call. Some time back I was also faced similar problem,i searched a lot, then i found solution. below I am sharing the solution.

Below is the postgresql function

Create or Replace Function myfunction(id integer) Returns Table(empid integer,empname charactervarying) AS
$BODY$
DECLARE
var_r record;

BEGIN
FOR var_r IN(
select * from emp where empno=id
)

LOOP
empid:=var_r.empno;
empname:=var_r.empname;
RETURN NEXT;
end LOOP;

END; $BODY$
LANGUAGE plsql VOLATILE

when we will run the query in postgresql console, it will give the result according to the records present in the table for id=5

select myfunction(5);

Now we have to write code to call this function from java. below is the code

public class Employee {
    public static void main(String[] args) {

    try {
    callProc();
    }Catch(SQLException e) {
    e.printStackTrace();
    }
    }

    public static void callProc () throws SQLException {



        Connection con=DBConnection.getConnection();
        Statement statement=con.createStatement();
    //Call to postgresql function   
        String query=" call myfunction(?)";
        CallableStatement ps=con.prepareCall(query);
        ps.setInt(1,5)  // it means we are setting value 5 at first index.
        ResultSet rs=ps.executeQuery();
        List<Emp> listOfEmp=new ArrayList<Emp>();

        while(rs.next()){

        Emp data=new Emp();
        data.setEmpno(rs.getInt("empid"));
        data.setEmpName(rs.getString("empid"));
        listOfEmp.add(data);

        }
        system.out.println("Total emp"+listOfEmp.size());
    }
}

In above example we have written the code to call the postgresql function from java.

for more details see this Postgresql Function with java

I know this is a bit old, but I stumbled upon the same problem and found a solution myself. It was pretty straightforward, but I hope this works for future reference.

CallableStatement statement = connection.prepareCall(" { call function( ?, ?, ? ) } ");

statement.setInt(1, value);
statement.setInt(2, value);
statement.setInt(3, value);
statement.execute();
statement.close();

Notice that there isn't a ? = before the call in the actuall sql code and that there is no call to registerOutParameter method.

Use createNativeQuery In your transaction write this and change myschema.mymethodThatReturnASelect for the scheme and the name of your function.

@Override
    public List<ViewFormulario> listarFormulario(Long idUsuario) {
        List<ViewFormulario> list =null;
        try {
            Query q = em.createNativeQuery("SELECT * FROM myschema.mymethodThatReturnASelect(?);");
            q.setParameter(1, idUsuario);

             List<Object[]> listObject = (List<Object[]>) q.getResultList();
            if (listObject != null && !listObject.isEmpty()) {
                list = new ArrayList<>();
                for (Object o[] : listObject) {
                    ViewFormulario c = new ViewFormulario();
                    c.setIdProyecto(o[0] != null ? Long.valueOf(o[0].toString()) : -1L);
                    c.setIdUsuario(o[1] != null ? Integer.valueOf(o[1].toString()) : -1);
                    c.setIsCreador(o[2] != null ? Boolean.valueOf(o[2].toString()) : null);
                    c.setIdPadre(o[3] != null ? Long.valueOf(o[3].toString()) : -1L);
                    c.setNombre(o[4] != null ? o[4].toString() : "");
                    c.setNombreCliente(o[5] != null ? o[5].toString() : "");
                    c.setCodigo(o[6] != null ? o[6].toString() : "");
                    c.setEstado(o[7] != null ? Long.valueOf(o[7].toString()) : -1L);
                    c.setTotalDisponible(o[8] != null ? Double.valueOf(o[8].toString()) : 0.0);
                    c.setCantidadAlmacenamiento(o[9] != null ? Double.valueOf(o[9].toString()) : 0.0);
                    c.setPorcentajeAlmacenamiento(o[10] != null ? Double.valueOf(o[10].toString()) : 0.0);
                    c.setCantidadUsuario(o[11] != null ? Long.valueOf(o[11].toString()) : 0);
                    c.setCantidadRegistros(o[12] != null ? Long.valueOf(o[12].toString()) : 0);
                    c.setMbFoto(o[13] != null ? Double.valueOf(o[13].toString()) : 0.0);
                    c.setMbVideo(o[14] != null ? Double.valueOf(o[14].toString()) : 0.0);
                    c.setMbTexto(o[15] != null ? Double.valueOf(o[15].toString()) : 0.0);
                    list.add(c);
                }
            }
        } catch (Exception e) {
            System.out.println("listarFormulario " + e.getMessage());
        }
        return list;
  }