How to register new function differently implemented in supported dialects?

[floresek]

Hi,

I want to add new function which implementation differs in supported dialects.

F.e. I want to execute query:

select instr(column1, '.') from table1
-- oracle dialect
select position('.' in column1) from table1
-- postgres dialect
--etc

on all supported DB
Function instr is not in sql* standard so each DB can implement it differently (function name and parameters).

I can see that polydb has dialog support.
Could you explain how to add function:

int position(String columnName, String textToFind)

to be supported in many DB, please?

Cheers
Mariusz

[adamdubiel]

Hm, it supports dialects at keyword level, but i never thought of adding support for functions. Probably you would need to extend SelectQuery, but this might get a bit sloppy. Other possibility would be to add bit more generic functions support, but it would need some more thought.

[floresek]

The simpler the better ;)

Could you suggest the simplest way?

[floresek]

BTW - I want to change current way of resolving max CommitId in javers.

Current query (basing on max pk commit id) from time to time crashes (because max px commit id is not max)

So I need to use functions to convert string commit id to number (to select max from DB query)

[bartoszwalacik]

why you think that max(to_number(commit_id)) would be more max than simply max(commit_pk)?

[floresek]

Thanks for suggestion. Finally I tried to use:

max(cast(commit_id as decimal(15,2)))

but in Oralce casting is nls_numeric_characters sensitive and when set nls to ',' exception is thrown - invalid number ;(