User defined PostgreSQL function in hibernate dialect throws exception

Question

Is it possible to register a custom function written in database & written in extended hibernate Postgres Dialect as follows? Receiving function not exists exception on using this function in HQL.

Postgres function:

    create or replace function ADD_DAYS(varDate timestamp without time zone, varNumber numeric) 
     returns timestamp without time zone 
     LANGUAGE sql AS    
    $$ 
     SELECT (varDate + varNumber * INTERVAL '1 day')
    $$;

Java code:

registerFunction("add_days", new SQLFunctionTemplate(StandardBasicTypes.DATE, "add_days(?1 , ?2)"));

Any hibernate configuration is ok with this. Dialect need be custom dialect which extends Postgre specific dialect. For the date fields TemporalType.TIMESTAMP & columnDefinition as date used. — Sreejesh.K.R.
– Sreejesh.K.R., Commented Jun 25, 2020 at 9:52
Ok, I'll ask more specific question: what value do you use for the hibernate.connection.url? Do you use hibernate.default_schema? — SternK
– SternK, Commented Jun 25, 2020 at 9:57
Not using default schema. I'll try with solution you mentioned. Schema might come to rescue (Y) — Sreejesh.K.R.
– Sreejesh.K.R., Commented Jun 26, 2020 at 12:28

SternK · Accepted Answer · 2020-06-25 12:27:38Z

1

I have faced with the similar problem. The problem was in the following:

The function was created in the particular schema TEST_SCHEMA. When I used the following configuration:

<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/postgres</property>
<property name="hibernate.default_schema">TEST_SCHEMA</property>

I got:

org.postgresql.util.PSQLException: ERROR: function levenshtein(character varying, character varying) does not exist. No function matches the given name and argument types. You might need to add explicit type casts.

But, when I specified the default schema explicitly in the connection url like below

<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/postgres?currentSchema=TEST_SCHEMA</property>

my function became visible.

edited Jun 25, 2020 at 12:27

answered Jun 25, 2020 at 10:41

SternK

13.3k34 gold badges40 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

User defined PostgreSQL function in hibernate dialect throws exception

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related