In PostgreSQL81Dialect, the function “age“ is registered as follows:
{{registerFunction( "age", new StandardSQLFunction("age") );}}
The documentation for StandardSQLFunction states that “Using this form, the return type is considered non-static and assumed to be the type of the first argument.“
The [PostgreSQL documentation|https://www.postgresql.org/docs/8.4/functions-datetime.html] states that the return type for this function is an interval while taking one or two timnestamps.
When using the function in an HQL,
{code:java}String hql = "SELECT " + "function('AGE', :dueDay, pv.birthdate)" + " as age, pv.sex, COUNT(*) " + "FROM PvDb pv" + "WHERE (pv.deleted IS NULL OR pv.deleted > :dueDay) " + "GROUP BY pv.birthdate, pv.sex" Query<Object[]> query = session.createQuery(hql, Object[].class); query.setParameter("dueDay", dueDay);{code}
an exception is thrown:
{quote}org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode +-\[METHOD_CALL] MethodNode: 'function (age)' | +-\[METHOD_NAME] IdentNode: 'age' \{originalText=age} | \\-\[EXPR_LIST] SqlNode: 'exprList' | +-\[NAMED_PARAM] ParameterNode: '?' \{name=testDate, expectedType=null}{quote}
Setting the parameter as string
{noformat} query.setParameter("dueDayAsString", dueDayAsString, StandardBasicTypes.STRING);{noformat}
gives the same exception.
It seems that it is not possible using Postgres’s age function with the current definition.
Providing a custom dialect,
{code:java}public class CustomDialect extends PostgreSQL95Dialect {
public CustomDialect() { super(); registerFunction("age", new StandardSQLFunction("age", StandardBasicTypes.STRING)); } }{code}
the problem is solved. |
|