Creating User-Defined Functions (UDF) with SQL

Some SQL environments allow to create so called user-defined functions (UDFs) to expand the coding capabilities beyond the default functions. Once specified, UDFs can be repeatedly called just like any other functions (SELECT, FROM etc.).

Here's an example of a UDF code that calculates age based on a selected date of birth:


CREATE FUNCTION AGE(dateofbirth DATE)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
SELECT
DATEDIFF(YEAR, dateofbirth, CURRENT_DATE()) AS age
$$

We define a UDF called age that takes a dateofbirth parameter of type Date and returns an Integer.

The function is defined using the LANGUAGE SQL clause, indicating that it's written in SQL.

We use $$ to delimit the start and end of the function body.

Inside the function body, we use the DATEDIFF function to calculate the difference in years between the dateofbirth and the current date (CURRENT_DATE()), which gives us the age of the person.

After creating the function, we can use it within SQL queries just like built-in functions.

SELECT
name
,dateofbirth
,AGE(dateofbirth) AS age
FROM employees

This query retrieves the name and date of birth of employees from a table named employees and calculates their age using the AGE UDF.

Author:
Michał Mioduchowski
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2024 The Information Lab