Relational databases are some of the most common and powerful database systems in the modern world. One of the things that all relational databases share is the use of SQL.
Standard Query Language or SQL, as we know it, is a powerful and flexible language for interacting, managing, and manipulating the data that is stored in a relational database.
There is one powerful feature in SQL that allows us to define the custom function which is also known as SQL functions. A custom function in SQL allows us to perform a given task within the database. The function can perform a wide variety of operations from querying the data from the database to manipulating the existing data.
In this guide, we will learn how to work with the CREATE FUNCTION statement in SQL to learn how we can create custom functions.
Requirements:
Before we begin, ensure that you have the following requirements met:
- An SQL variant (MySQL, PostgreSQL, SQLite, etc.)
- Permissions to create functions in the target database server
With the given requirements met, we can proceed.
SQL Functions
SQL functions are very similar to a typical function in other programming languages. It is simply a way of creating reusable code blocks that can accept the parameters, perform a specific task, and return a value or a result.
We can group the SQL functions into two main categories:
- Scalar Functions – The scalar functions return a single value based on the input parameters. A common use of scalar functions is calculating the values or data transformations.
- Table-Valued Functions – TFVs, on the other hand, return a table as an output. A common use of TVDs is when working with very complex queries. If you need a function that can return a set of records, table-valued functions are a good tool.
Create a Function Statement in SQL
In SQL, we use the CREATE FUNCTION statement to create a user-defined function in SQL. The following shows the syntax for the CREATE FUNCTION statement in SQL:
RETURNS rt_data_type
BEGIN
-- function body
END;
We start with the CREATE FUNCTION keyword to indicate that we wish to create a new custom function. We also ensure to pass the name of the function.
Next, we provide the function input parameters inside a pair of parentheses. A function can have zero or more parameters.
To define a parameter, we start with the parameter name and the data type of the function that the function should accept.
We then proceed to set the return value of the function. We do this by setting the data type that the function should return. For example, if the function returns a number, you can specify an appropriate return value such as INT.
Next, we use the BEGIN keyword to denote the start of the function body. In the function body, we provide all the tasks that the function should perform.
Lastly, we close the function body using the END keyword.
Example 1:
Let us look at a practical example that demonstrates how to use the CREATE FUNCTION statement to define a new SQL function.
Suppose we have a function that determines the logarithm of a given input number. In SQL, we can define the function as follows:
CREATE FUNCTION calculate_logarithm(input_value FLOAT)
RETURNS FLOAT
DETERMINISTIC
BEGIN
IF input_value IS NULL OR input_value <= 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '[err]: Invalid input.';
END IF;
RETURN LN(input_value);
END$$
DELIMITER ;
In this example, we start by temporarily changing the delimiter that is used in the SQL queries. This is a common task in MySQL to help distinguish between the SQL statements inside the function and the function definitions.
Next, we create a function that calculates the natural logarithm of a given input value. The function returns a float.
NOTE: Notice the use of the DETERMINISTIC keyword which indicates that the function will return the same result for the same input. This plays a crucial role in query optimization.
In the function body, we check if the input is a positive value and return an error if it is false. Otherwise, we calculate the logarithm of the value and return the output.
Finally, we use the END$$ to denote the end of the function's logic.
NOTE: We also use the DELIMITER statement to reset the delimiter to default.
Once we define the function, we can call the function as follows:
This should call the function with the specified input and return the corresponding logarithm value or error if the input is invalid.
Example 2:
We can also create a TVF that returns a table as demonstrated in the following example:
RETURNS TABLE (
db_id INT,
db_name VARCHAR(255)
)
BEGIN
RETURN (
SELECT db_id, db_name
FROM databases
WHERE class_id = c_id
);
END;
SELECT * FROM FetchDatabases("document");
In this code, the function returns all the records where the database class is equal to “document”. This returns all the matching records from the databases table as a table with “db_id” and “db_name” columns.
Conclusion
In this tutorial, we learned the workings of the CREATE FUNCTION statement in SQL which allows us to create a custom encapsulated logic that we can reuse across the database.
