Create and use database function with JPA

Question

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 ?

The rules on my project does not allow the use stored procedure — Torarn
– Torarn, Commented Nov 16, 2020 at 16:16

Jeroen Steenbeeke · Accepted Answer · 2020-11-16 16:18:09Z

1

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();

edited Nov 16, 2020 at 16:18

answered Nov 16, 2020 at 15:58

Jeroen Steenbeeke

4,0585 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Torarn Over a year ago

I tried it too, but then i get nearly the same exception but with the "Caused by" being "org.postgresql.util.PSQLException: A result was returned when none was expected"

Jeroen Steenbeeke Over a year ago

Really? Wow, I did not see that one coming. So if you try getSingleResult it doesn't return results, and if you try executeUpdate it does return results?

Torarn Over a year ago

yes, that's why i can't get it. I tried combination of request like

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

or

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

and with executeUpdate, getSingleResult or getResultList, but none worked

Jeroen Steenbeeke Over a year ago

Ah, I think I see the problem. You're trying to execute two statements in one command. I'll update my answer

Torarn Over a year ago

I see. It works fine that way. So there is no way i can get it work with only one call ? My statement is originaly from a constant so i would prefere not to split it. Is there a way to ignore the result of the function creation so that it keeps on executing the second statement ?

|

Collectives™ on Stack Overflow

Create and use database function with JPA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related