In PostgreSQL, creating a user-defined function assist us in defining customized logic and operations that can be executed in a database when needed. For this purpose, the CREATE FUNCTION statement is used in PostgreSQL. User-defined functions allow us to accomplish a particular task with ease. Moreover, creating a user-defined function help us encapsulate our own custom logic directly in the database.

This write-up illustrates how to use the CREATE FUNCTION statement in Postgres.

How Does CREATE FUNCTION Work in PostgreSQL?

The CREATE FUNCTION statement allows us to create a user-defined function by specifying its name, input parameters, return type, and the language used for its implementation. Execute the following syntax to create a user-defined function:

CREATE [OR REPLACE] FUNCTION func_name ([par_1 data_type [, par_2 data_type [, ...]]])
RETURNS return_type 
LANGUAGE plpgsql
AS
$$
-- Function body
$$

Let's comprehend the CREATE FUNCTION statement line by line:

• Specify any valid name of your choice in place of func_name.
• “par_1, par_2, …” represents the function parameters that have a specific “data_type”.
• “RETURNS return_type” denotes the data type that will be retrieved by the function.
• “LANGUAGE plpgsql” represents the procedural language PL/pgSQL in which the function will be written.
• The “AS” keyword indicates the start of the function body.

Sample Table

The following snippet depicts the data of the sample table named “emp_bio”:

SELECT * FROM emp_bio;

Example: How to Create a User-defined Function in Postgres?

Let’s create a new user-defined function and named it “emp_count”:

CREATE FUNCTION emp_count (sal_from INTEGER, sal_to INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS
$$
DECLARE
total_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO total_count
FROM emp_bio
WHERE emp_sal between sal_from and sal_to;
RETURN total_count;
END;
$$;

In the above code snippet:

• A user-defined function named “emp_count” is created that accepts two parameters.
• The “emp_count” will retrieve an integer value.
• A variable named “total_count” is created that will keep the number of employees selected from the emp_bio table.
• The SELECT INTO statement will select the employees whose salary is between “emp_from” to “emp_to” and assign them to the variable “emp_count”.
• The RETURN statement will retrieve the number of employees:

A function named “emp_count” has been successfully created. Let’s call the newly created user-defined function by specifying the argument in the same order as the parameter’s order:

SELECT emp_count(45000,50000);

The function retrieves “3” which indicates that there are three employees in the emp_bio table whose salary is between “45000” to “50000”.

Conclusion

In PostgreSQL, the CREATE FUNCTION statement is utilized to develop/create a user-defined function. It allows us to create a user-defined function by specifying its name, input parameters, return type, and the language used for its implementation. A user-defined function can be invoked using the SELECT statement. This post has illustrated a complete guide on creating a user-defined function in Postgres.