Postgres function calls do not work any more for postgres versions < 17.

[chicko12345]

Hi Community,

the change introduced by
#2308
leads to unexpected behavior in our environment. Postgres functions cannot be triggered as it was in eclipselink 4.0.4
The commit removes specific handling of postgres function calls which seem to be necessary for postgres versions < 17.

Local revert of:

• Revert "Fix calling stored procs on Postgres, and enable some tests (Fix calling stored procs on Postgres, and enable some tests #2308)"

This reverts commit 452ffdd.

fixes the problem.

Our environment:

• Glassfish 7.0.22
• EclipseLink 4.0.5
• Java/JDK version JavaSE 21
• Postgres 16.6

To Reproduce
If a postgres function is defined

create function some_fuction(param1 text, param2 integer, param3 integer) returns void
    language plpgsql
as
$$
BEGIN
 // Do something
end;
$$;

Both following versions of the function call throw SQL Exceptions:

//def and set param1, param2, param3, open transaction
StoredProcedureQuery storedProcedure = perServ.createStoredProcedureQuery("SOME_FUNCTION", void.class);
storedProcedure.registerStoredProcedureParameter("param1", String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("param2", Integer.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("param3", Integer.class, ParameterMode.IN);
storedProcedure.setParameter("param1", param1);
storedProcedure.setParameter("param2", param2);
storedProcedure.setParameter("param3", param3);
storedProcedure.execute();

//def and set param1, param2, param3, open transaction
try (Connection c = /*create connection*/)){ 
  c.setAutoCommit(false);
  try (CallableStatement cs = c.prepareCall("{call SOME_FUNCTION(?,?,?)}")) {
	cs.setString("param1", param1);
	cs.setInt("param2", param2);
	cs.setInt("param3", param3);
	cs.execute();      
  }
  c.commit();
}
//Exception handling			
...

Kind Regards

[rfelcman]

To @chicko12345 @rdicroce
Hello as I don't know all PostrgreSQL tricks. How is possible to call this kind of stored function
CREATE FUNCTION test_fuction1(param1 text, param2 INTEGER, param3 INTEGER) RETURNS VOID
and stored procedure
CREATE PROCEDURE test_procedure1(param1 text, param2 INTEGER, param3 INTEGER)
with same call statement like CALL " + <FUNCTION_NAME OR PROCEDURE_NAME> + "(?,?,?).
CALL PROCEDURE_NAME(?,?,?) works for stored procedures, but without curly brackets around
{CALL FUNCTION_NAME(?,?,?)} works for stored function, but with curly brackets around

[rdicroce]

I'm not an expert on PG. My company just started using it, and I've since been pulled off to work on other things. But I know that stored function != stored procedure in PG, and you can't call them the same way. IIRC for stored procs, you need to do what my patch does. For stored funcs, you need to use SELECT.