SQL Standard

Create a Function in SQL

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.


Before we begin, ensure that you have the following requirements met:

  1. An SQL variant (MySQL, PostgreSQL, SQLite, etc.)
  2. 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:

  1. 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.
  2. 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:

CREATE FUNCTION func_name ([pa1 data_type, par data_type, ...])
RETURNS rt_data_type
    -- function body


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)
    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);


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:

SELECT calculate_logarithm(100);


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:

    db_id INT,
    db_name VARCHAR(255)
    RETURN (
        SELECT db_id, db_name
        FROM databases
        WHERE class_id = c_id

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.


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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list