Creating a UDF

Users who want to take the advantage of the UDF capability can create UDF functions using the following syntax:

CREATE FUNCTION [ owner_name.]udf_name ( [ parameter_decl [ , ... ] ] )
RETURN data_type
[IMPORT java_import_clause ]
BEGIN
[ java_snippet ]
END
parameter_decl:= parameter_name data_type

The following example shows how to create a UDF and use it in a SELECT function. In this example, the UDF, which is an EXTRACT function, can be used to return a single part (year, month, day, hour, minute) of a date /time stamp of a certain event. This data could be used to compare or calculate a time period or a certain delay that might have occurred between the two events.

CREATE function EXTRACT(extractfield varchar(20), col timestamp)
return integer
import
import java.util.Date;
begin
extractfield=extractfield.toLowerCase();
switch(extractfield)
{
case "date" : return col.getDate();
case "month" : return col.getMonth();
case "year" : return col.getYear();
case "time" : return (int)col.getTime();
case "minute" : return (int)col.getMinutes();
case "hour" : return (int)col.getHours();
case "second" : return (int)col.getSeconds();
case "day" : return col.getDay();
case "default" : return -1;
}
return 1;
end

SELECT EXTRACT('date' , orderdate) from pub.order;
EXTRACT(date,OrderDate)
-----------------------
9
27
SELECT EXTRACT('day' , orderdate) from pub.order;
EXTRACT(day,OrderDate)
----------------------
3
5
SELECT EXTRACT('month' , orderdate) from pub.order;
EXTRACT(month,OrderDate)
------------------------
1
2