JPA & PostgreSQL: How do I call a stored procedure with void return type?

Question

I'm trying to call a PL/pgSQL function that executes an update (language sql, returns void). I'm getting this exception:

Internal Exception: org.postgresql.util.PSQLException: A result was returned when none was expected.

persistence.xml:

<named-native-query name="Clinic.deactivateByNotFoundInIncomingClinic">
    <query>
        <![CDATA[
            select apply_incoming_clinic_deletions(?)
        ]]>
    </query>
</named-native-query>

DAO:

public void deactivateByNotFoundInIncomingClinic(long clinicSystemId)
{
    em.createNamedQuery("Clinic.deactivateByNotFoundInIncomingClinic")
            .setParameter(1, clinicSystemId)
            .executeUpdate();
}

Help!

Update:

Using EclipeseLink on GlassFish 3.1.1 talking to PostgreSQL 9.0 database via PostgreSQL 9.0-801 JDBC 4 drivers.

Answer 1

Not sure where this comes from. Your function definition is missing in the question.
But you could try a plpgsql function instead of sql. Their return type is subtly different when declared as RETURNS void. Consider this demo:

CREATE OR REPLACE FUNCTION f_sql()
  RETURNS void AS
'UPDATE foo SET id = id+1 WHERE id = 34567'
  LANGUAGE sql;

CREATE OR REPLACE FUNCTION f_plpgsql()
  RETURNS void AS
$$
BEGIN
UPDATE foo SET id = id+1 WHERE id = 34567;
END;
$$  LANGUAGE plpgsql;

Now, VOID is fictive type. While the plpgsql function actually returns VOID, the SQL function seems to return NULL. I am not sure myself why this is.

db=# SELECT f_sql() IS NULL;
 ?column?
----------
 t

db=# SELECT f_plpgsql() IS NULL;
 ?column?
----------
 f