Using Specification CriteriaBuilder with non-standard operators like % from pg_trgm #575
-
Is there any way I can make a I'm using pg_trgm, which has this operator as a way to implement fuzzy search. I can't just make a function around it, since that disables index scanning. I need something similar to SELECT t, similarity(t, 'word') AS sml
FROM test_trgm
WHERE t % 'word' -- this operator enables an index scan
ORDER BY sml DESC, t; Is there something like |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 2 replies
-
Definitely a perfect use case for a native SQL query. There's no way that query is ever going to work on any other database, so you lose very little by writing it in SQL. |
Beta Was this translation helpful? Give feedback.
-
You can define an immutable inline function in PostgreSQL for the operator and use that instead, which will make use of the index. Alternatively, you can use the Lastly, you can also register custom functions in Hibernate ORM and EclipseLink that render whatever SQL that you want. You use them through |
Beta Was this translation helpful? Give feedback.
You can define an immutable inline function in PostgreSQL for the operator and use that instead, which will make use of the index.
Alternatively, you can use the
sql
bultin function throughCriteriaBuilder.function()
which is supported by Hibernate ORM and EclipseLink. That allows you to specify a string representing a SQL template with placeholders as first argument followed by varargs that are filled into that template. Take a look into the Hibernate ORM or EclipseLink documentation for more details.Lastly, you can also register custom functions in Hibernate ORM and EclipseLink that render whatever SQL that you want. You use them through
CriteriaBuilder.function()
as well.