# CREATE FUNCTION Statement in PostgreSQL

> The CREATE FUNCTION statement allows us to create a user-defined function by specifying its name, parameters, return type, and the language to be implemented.

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.

---
[View this page online](https://www.commandprompt.com/education/create-function-statement-in-postgresql/)