pass root as function attribute criteria query

Question

I have a postgreSQL custom function that requires one attribute of type "my_entity".

In SQL I can call this function normally:

select my_function(t) from my_entity t;

Now what I need is call this same function using CriteriaQuery, but when I try, hibernate generates a SQL statement with my_table.id instead of the tuple itself:

select my_function(my_entity0_.id) from my_entity my_entity0_;

My code:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<MyEntity> cq = cb.createQuery(MyEntity.class);
Root<MyEntity> root = cq.from(MyEntity.class);

Expression<String> myFunction= cb.function("my_function", String.class, root);
cq.select(myFunction);

return em.createQuery(cq).getResultList();

Function:

CREATE OR REPLACE FUNCTION my_function(
      IN "T" my_entity)
RETURNS character varying
LANGUAGE 'plpgsql'

AS $BODY$BEGIN

    ... some logic ...

    RETURN some_calculated_result;

END;$BODY$;

How to pass correctly the argument to this function?

Answer 1

I just give up and created a new function which, instead of an entity/row, it requires N arguments:

Expression<String> calcSubStatus = cb.function("my_new_function", String.class,
                attr1, attr2, attr3, attr4, attr5, attrN);

CREATE OR REPLACE FUNCTION my_new_function(
      IN "ATTR1" character varying,
      IN "ATTR2" character varying,
      IN "ATTR3" character varying,
      IN "ATTR4" character varying,
      IN "ATTR5" character varying,
      IN "ATTRN" character varying)
RETURNS character varying
LANGUAGE 'plpgsql'

AS $BODY$BEGIN

    ... some logic ...

    RETURN some_calculated_result;

END;$BODY$;