# Custom function type cast problem

**URL:** <https://discourse.hibernate.org/t/custom-function-type-cast-problem/11389>\
**Category:** Hibernate ORM\
**Created:** [April 24, 2025, 1:43pm UTC](https://discourse.hibernate.org/t/custom-function-type-cast-problem/11389 "2025-04-24T13:43:25Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![grindnoise](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/grindnoise/32/3987_2.png) [@grindnoise](https://discourse.hibernate.org/u/grindnoise)\
**Post date:** [April 24, 2025, 1:43pm UTC](https://discourse.hibernate.org/t/custom-function-type-cast-problem/11389/1 "2025-04-24T13:43:25Z")

</div>

Hi!  
Due to Spring Boot update Hibernate was updated from 6.5.2.Final to 6.6.11.Final

I’ve registered custom function:

```auto
public class ArrayContainsArraySQLFunction extends StandardSQLFunction {
    private static final BasicTypeReference<Boolean> RETURN_TYPE = new BasicTypeReference<>("boolean", Boolean.class, SqlTypes.BOOLEAN);

    public ArrayContainsArraySQLFunction(final String functionName) {
        super(functionName, true, RETURN_TYPE);
    }

    @Override
    public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> sqlAstArguments, ReturnableType<?> returnType, SqlAstTranslator<?> translator) {
        sqlAstArguments.get(0).accept(translator);
        sqlAppender.append(" @> ");
        sqlAstArguments.get(1).accept(translator);
    }
}

```

So I used it in specification like so:

```auto
        static Specification<MailingList> byEmailList(List<String> emailList) {
            return (root, query, cb) ->
                    cb.isTrue(
                            cb.function("array_contains_all", Boolean.class,
                                    root.get(MailingList_.recipientList),
                                    cb.literal(emailList.toArray(new String[0])).as(String[].class)
                            ));
        }

```

In 6.5.2.Final version it worked fine, producing SQL query (PostgreSQL):

```auto
Hibernate: 
    select
        ml1_0.id,
        ml1_0.operation,
        ml1_0.recipient_list 
    from
        mailing_list ml1_0 
    where
        ml1_0.recipient_list @> cast(ARRAY['test@test.com'] as varchar array)

```

But in 6.6.11 it doesn’t add explicit type casting **as varchar array** :

```auto
Hibernate: 
    select
        ml1_0.id,
        ml1_0.operation,
        ml1_0.recipient_list 
    from
        mailing_list ml1_0 
    where
        ml1_0.recipient_list @> ARRAY['test@test.com']

```

And prints error:

> `org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying[] @> text[]`  
> `Hint: No operator matches the given name and argument types. You might need to add explicit type casts.`

How to fix it?  
Thanks.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [April 29, 2025, 3:08pm UTC](https://discourse.hibernate.org/t/custom-function-type-cast-problem/11389/2 "2025-04-29T15:08:54Z")

</div>

Hello @grindnoise, Hibernate 6.6 natively supports array functions. You can use the `HibernateCriteriaBuilder` extension APIs and its native `arrayContains` method, without the need for any custom function registration.

---

<div class="post-metadata">

**Author:** ![grindnoise](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/grindnoise/32/3987_2.png) [@grindnoise](https://discourse.hibernate.org/u/grindnoise)\
**Post date:** [April 30, 2025, 6:49am UTC](https://discourse.hibernate.org/t/custom-function-type-cast-problem/11389/3 "2025-04-30T06:49:45Z")

</div>

Hi! Thanks, but what if I need to add explicit type cast for custom function?  
Not concerning this case

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [May 7, 2025, 3:01pm UTC](https://discourse.hibernate.org/t/custom-function-type-cast-problem/11389/4 "2025-05-07T15:01:14Z")

</div>

If you need an _explicit_ cast operation, there’s `org.hibernate.query.criteria.HibernateCriteriaBuilder#cast`.