# User Defined Functions in Dialect

**URL:** <https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962>\
**Category:** Hibernate ORM\
**Created:** [November 29, 2022, 6:00am UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962 "2022-11-29T06:00:45Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![tspear](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tspear/32/2140_2.png) [@tspear](https://discourse.hibernate.org/u/tspear)\
**Post date:** [November 29, 2022, 6:00am UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/1 "2022-11-29T06:00:45Z")

</div>

Looking to migrate from Hibernate 5.X to 6.1.  
I have a custom Dialect where I register a number of functions in the constructor: e.g

> registerFunction(“ecfts”, new PgFullTextSearchFunction());

This approach is no longer supported. From what I can find, it looks like I will need to implement something along the lines of a FunctionContributor. However, I have not had any luck finding a working example, and if this is actually the best practice going forward.  
Any suggestions?

Thanks,

Tim

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 29, 2022, 7:14am UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/2 "2022-11-29T07:14:50Z")

</div>

I just recently answered a similar question: [Migration of dialect to Hibernate 6](https://discourse.hibernate.org/t/migration-of-dialect-to-hibernate-6/6956)

---

<div class="post-metadata">

**Author:** ![tspear](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tspear/32/2140_2.png) [@tspear](https://discourse.hibernate.org/u/tspear)\
**Post date:** [November 29, 2022, 6:05pm UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/3 "2022-11-29T18:05:58Z")

</div>

Ok, I have now read that thread. They are not registering an actual user defined function, and they are not manipulating the generated SQL to call and utilize a user defined function in the database. All they are doing is registering known functions of the database and changing return types.

However, I cannot find any documentation, or examples to register a user defined function. I have searched on `FunctionContributor` and Hibernate, user defined functions… All answers point back to Hibernate 4 or 5.

Even going through the code, I have yet to find an example where someone has implemented the equivalent of the render method from SQLFunction in Hibernate 5.X

An example of how I used render in the 4.X and 5.X versions.

```auto
	@Override
	public String render(Type firstArgumentType, @SuppressWarnings("rawtypes") List args, SessionFactoryImplementor factory) throws QueryException {
		if (args.size() != 2) throw new IllegalArgumentException("The function must be passed 2 arguments");
		String field = (String) args.get(0);
		String value = (String) args.get(1);
		value = value.replaceAll("\\s+", " & ").replaceAll("'", "");
		value = org.apache.commons.lang3.StringUtils.stripAccents(value);
		StringBuilder fragment = new StringBuilder();
		fragment.append("to_tsvector(").append(field).append(")");
		fragment.append(" @@ ");
		fragment.append("websearch_to_tsquery(" + value + ")");
		if (log.isDebugEnabled()) log.debug("FullTextSearch fragment:" + fragment);
		return fragment.toString();				
	}

```

---

<div class="post-metadata">

**Author:** ![tspear](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tspear/32/2140_2.png) [@tspear](https://discourse.hibernate.org/u/tspear)\
**Post date:** [November 29, 2022, 9:37pm UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/4 "2022-11-29T21:37:44Z")

</div>

@beikov

I believe I found your code in org.hibernate.dialect.function.SqlFunction  
I think this will likely be a good starting point. I still need to back trace the code enough to understand enough to implement my own function. I did not dig yet into the `META-INF/services/org.hibernate.boot.model.FunctionContributor` aspect mentioned in the other thread.

I believe you implied the META-INF is the direction intended for best practice for user defined functions

Am I going in the correct direction?

Thanks,

Tim

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 30, 2022, 4:04am UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/5 "2022-11-30T04:04:47Z")

</div>

Yes, registering functions should happens through the `FunctionContributor`. Note that when the function return type is `Boolean`, you can use it as predicate directly i.e. in HQL `where matches(...)`.  
You can render the fragment in a custom implementation, or you register a pattern function:

```auto
functionContributions.getFunctionRegistry().registerPattern(
    "myFunction", 
    "to_tsvector(?1) @@ websearch_to_tsquery(?2)",
    functionContributions.getTypeConfiguration().getBasicTypeRegistry()
        .resolve(StandardBasicTypes.BOOLEAN)
);

```

---

<div class="post-metadata">

**Author:** ![tspear](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tspear/32/2140_2.png) [@tspear](https://discourse.hibernate.org/u/tspear)\
**Post date:** [November 30, 2022, 6:21pm UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/6 "2022-11-30T18:21:39Z")

</div>

I looked at the registerPattern actually. It does not work since I need to remove accents, and other special characters which break the PostgreSQL text search functions.

I know I am missing something trying to back trace the code since my FunctionContributor does not work. With the many levels of indirection utilized in the core Hibernate code, (the abstractions tend to make sense once you understand them, but getting there…) I think this will take me a couple days to figure out.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 1, 2022, 10:18am UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/7 "2022-12-01T10:18:21Z")

</div>

Maybe you should use the `replace` SQL function to let PostgreSQL do this? Otherwise your implementation only works if you pass literals.

```java
functionContributions.getFunctionRegistry().registerPattern(
    "myFunction", 
    "to_tsvector(?1) @@ websearch_to_tsquery(unaccent(replace(regexp_replace(?2,'\\s+', ' & '),'''', '')))",
    functionContributions.getTypeConfiguration().getBasicTypeRegistry()
        .resolve(StandardBasicTypes.BOOLEAN)
);

```

with [PostgreSQL: Documentation: 15: F.48.&nbsp;unaccent](https://www.postgresql.org/docs/current/unaccent.html)

You can implement a custom function similar to `SqlFunction` that tries to unwrap arguments though if you really want to do the same. You just cast the argument to `QueryLiteral` and append the value to the `SqlAppender` directly.

---

<div class="post-metadata">

**Author:** ![tspear](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tspear/32/2140_2.png) [@tspear](https://discourse.hibernate.org/u/tspear)\
**Post date:** [December 1, 2022, 8:11pm UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/8 "2022-12-01T20:11:19Z")

</div>

lol, I did not consider the PostgreSQL unaccent function.  
Hibernate starts now, and does not complain. However I cannot test yet, since I ran into a totally separate issue with my SpringBoot 3 upgrade.  
I will report back, after I get the other items resolved.

---

<div class="post-metadata">

**Author:** ![tspear](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tspear/32/2140_2.png) [@tspear](https://discourse.hibernate.org/u/tspear)\
**Post date:** [December 6, 2022, 4:03pm UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/9 "2022-12-06T16:03:40Z")

</div>

@beikov

I ended up switching paths a little and using your pattern function idea. I can give the gory details of why i switched if curious.

Documentation for the next person trying to solve user defined functions in Hibernate.

Here is my final class to register my two full text search functions:

```auto
public class PgFunctionContributor implements FunctionContributor {
	@Override
	public void contributeFunctions(FunctionContributions functionContributions) {
		BasicType<Boolean> resolveType = functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.BOOLEAN);
		functionContributions.getFunctionRegistry().registerPattern("ects","to_tsvector(?1) @@ websearch_to_tsquery(?2)",resolveType);
		functionContributions.getFunctionRegistry().registerPattern("ecfts","?1 @@ websearch_to_tsquery(?2)",resolveType);
	}
}

```

To register the functions:  
A text file with the fully qualified class names of all defined function contributors is required.  
The filename is /META-INF/services/org.hibernate.boot.model.FunctionContributor

Tim

---

<div class="post-metadata">

**Author:** ![Aryan\_Rishi\_Lamba](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/aryan_rishi_lamba/32/2744_2.png) [@Aryan\_Rishi\_Lamba](https://discourse.hibernate.org/u/Aryan_Rishi_Lamba)\
**Post date:** [October 27, 2023, 8:52pm UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/10 "2023-10-27T20:52:53Z")

</div>

@tspear @beikov could you please share the github repo link for the complete working of the code.

PS : do i need resiter **PgFunctionContributor** class in application.properties file?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [October 30, 2023, 10:13am UTC](https://discourse.hibernate.org/t/user-defined-functions-in-dialect/6962/11 "2023-10-30T10:13:28Z")

</div>

It’s a Java `ServiceLoader` contract, so read here about how to enabled this: [ServiceLoader (Java SE 9 & JDK 9 )](https://docs.oracle.com/javase%2F9%2Fdocs%2Fapi%2F%2F/java/util/ServiceLoader.html)