Scalar functions
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The most common type of user defined function is a scalar function, i.e. a function that returns a single scalar value. Such functions can be used in the SELECT clause, the WHERE clause, the GROUP BY clause, the HAVING clause, the ORDER BY clause, and elsewhere, where column expressions can be used.
A simple example for creating such a function is:
// Create a function that always return 1
create.createFunction("one")
.returns(INTEGER)
.as(return_(1))
.execute();
// Create a function that returns the sum of its inputs
Parameter<Integer> i1 = in("i1", INTEGER);
Parameter<Integer> i2 = in("i2", INTEGER);
create.createFunction("my_sum")
.parameters(i1, i2)
.returns(INTEGER)
.as(return_(i1.plus(i2)))
.execute();
Once you've created the above functions, you can either run code generation to get a type safe stub for calling them, or use plain SQL (specifically, DSL.function()) from within a SELECT statement:
// Call the previously created functions with generated code:
create.select(one(), mySum(1, 2)).fetchOne();
// ...or with plain SQL
create.select(
function(name("one"), INTEGER),
function(name("my_sum"), INTEGER, val(1), val(2))
).fetchOne();
Both yielding:
+-----+--------+ | ONE | MY_SUM | +-----+--------+ | 1 | 3 | +-----+--------+
Feedback
Do you have any feedback about this page? We'd love to hear it!