How to Create UDF in C Language

Simple UDF code, it is written in ‘C’ Language. UDFs are small programs that you can write to perform an operation. You can write user defined functions in SQL, C or any other supported language.

Sample UDF Create Function

CREATE FUNCTION DAYSINMONTH(DATE)
RETURNS INTEGER
EXTERNAL NAME 'DAYMTH'
LANGUAGE C;

This statement creates a UDF named DAYSINMONTH, with one parameter of DATE data type. It returns a single value of INTEGER data type. The external name for the function, that is the actual program name, is DAYMTH and it is coded in C.

Most of the parameters have been omitted from this simple CREATE FUNCTION example. Once the user-defined function has been created, and the appropriate authority has been Granted, the UDF can be used in an SQL statement,

for example:

SELECT EMPNO, LASTNME,
BIRTHDATE, DAYSINMONTH(BIRTHDATE)
FROM PAYROLL.EMP
WHERE DAYSINMONTH(BIRTHDATE) < 31;

The results of this statement would be a list of employees whose birth date falls in a month having fewer than 31 days (that is, February, April, June, September, and November). This assumes that the program for the user-defined function, DAYSINMONTH, is correctly coded to examine the date specified as input and return the actual number of days in the month.

References