"A result was returned when none was expected" exception on JDBCTemplate update execution

Question

I am trying to execute function via postgresql update() method, but it throws me an exception - "A result was returned when none was expected".

PostgreSQL function:

CREATE OR REPLACE FUNCTION create_order(note VARCHAR, created_by BIGINT, service_request BIGINT) 
RETURNS TABLE (service_order integer, note varchar)
BEGIN
  INSERT INTO service_order 
  (note, service_request_fk, created_by, so_status_type_fk, price_total, created)
  VALUES (note, service_request, created_by, 1, 0, now());
END;
$$ LANGUAGE plpgsql;

SQL, which i am trying to execute:

String sql = "SELECT create_order(?,?,?)";

The update function:

int id = jdbc.update(sql, new Object[] {order.getNote(), emp.getEmployeeId(), order.getServiceRequestId()});

The function must return VOID that means nothing, but it seems to me, that it returns table without rows, which JDBCTemplate consider as a table.

How can I avoid this exception?

Answer 1

As of PostgreSQL 9, there isn't a CALL command, so you have to use SELECT instead. Calling a stored procedure or function using SELECT func_name(); will return a RECORD. To return columns or a TABLE, you must change the syntax to SELECT * FROM func_name();