Reputation: 412
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 ?
Upvotes: 1
Views: 557
Reputation: 4048
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();
Upvotes: 1