H2 User Defined Functions

[Alex Feng]

Does H2 allow you to create user defined functions within the
database?

I want to do something like this:

insert into table1 values ('key1',to_date('MM/DD/YYYY','10/10/2011');

instead of changing all my code to replace the "to_date" function, I
want to write something in H2
that does the same thing as the Oracle to_date.

[Eric Faulhaber]

Yes, see http://www.h2database.com/html/features.html#user_defined_functions

[Sam Blume]

Hi  Alex

FYI; I've contributed the TO_DATE (and TO_TIMESTAMP) code to H2 and it should be in Version 1.4.191 (It's not mentioned in the change log :-( ). 

Note: If you have programed an alias with that name, you need to delete that alias first or H2 will complain of having a name clash.

Cheers Sam

[Suresh Prajapati]

Does H2 database support SQL user defined function (which is say 50 lines of code etc..) without converting such function in java method?

[Kerry Sainsbury]

Six years ago (gasp!) I wrote a patch for H2 that did this. You can read the conversation here.

Weirdly, I can't remember what happened in the end, or why it never got merged into trunk. I feel like a new project, so maybe I'll take another crack at it. I still think it's useful.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Kerry Sainsbury]

Although, to answer your question. yes it does:

http://www.h2database.com/html/features.html#user_defined_functions

eg:

CREATE ALIAS NEXT_PRIME AS $$
String nextPrime(String value) {
    return new BigInteger(value).nextProbablePrime().toString();
}
$$;

[Suresh Prajapati]

Hello Kerry,

Thanks for your feedback.

I think you may not get my question clearly. No prob, Let me Re-explain you again.

I dont want to convert whole SQL function logic into java code. I want to use my existing SQL function/procedure in H2 database. Is it possible without converting in java code because in real life, there are situation where we cannot always convert whole long function/procedure in java code for time concern and maintance?

Found information on link you provided http://www.h2database.com/html/features.html#user_defined_functions

"In addition to the built-in functions, this database supports user-defined Java functions."

Does it mean that we cannot write long SQL function/procedure in H2 database which is purely database function/procedure without replicating same logic in java?

With Regards,

Suresh.

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/5qg1irBij2E/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Noel Grandin]

On 27 May 2016 at 06:45, Suresh Prajapati <suresh....@gmail.com> wrote:

Does it mean that we cannot write long SQL function/procedure in H2 database which is purely database function/procedure without replicating same logic in java?

Yes

 

[Suresh Prajapati]

Thank You Very Much for updating me.

--