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 timestamps.
When using the function in an HQL,
{code:java} Date dueDay = new Date();
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} SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dueDayAsString = format.format(dueDay);
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. |
|
