Create and use database function with JPA

I want to create and immediatly use a database function in a single sql call.

As an example, the following works fine in PostgreSQL SQL Editor :

CREATE OR REPLACE FUNCTION pg_temp.get(IN nb BIGINT) RETURNS BIGINT AS 'SELECT $1 + $1' LANGUAGE sql IMMUTABLE;

SELECT pg_temp.get(2);

But when i try to use it with JPA :

getEntityManager().createNativeQuery("CREATE OR REPLACE FUNCTION pg_temp.get(IN nb BIGINT) RETURNS BIGINT AS 'SELECT $1 + $1' LANGUAGE sql IMMUTABLE;n" +
     "SELECT pg_temp.get(2);").getSingleResult();

I get the following error :

org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
    at org.hibernate.loader.Loader.doQuery(Loader.java:909)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
    at org.hibernate.loader.Loader.doList(Loader.java:2553)
    at org.hibernate.loader.Loader.doList(Loader.java:2539)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
    at org.hibernate.loader.Loader.list(Loader.java:2364)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495)
    ... 199 more
 Caused by: org.postgresql.util.PSQLException: No results were returned by the query.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:381)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
    ... 214 more

How could i make this work ?

Answer

You’re trying to execute two queries at once. You need to split this into two queries:

int updated = getEntityManager().createNativeQuery("CREATE OR REPLACE FUNCTION pg_temp.get(IN nb BIGINT) RETURNS BIGINT AS 'SELECT $1 + $1' LANGUAGE sql IMMUTABLE").executeUpdate();
Object result = getEntityManager().createNativeQuery("SELECT pg_temp.get(2)").getSingleResult();