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.
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:
The function retrieves “3” which indicates that there are three employees in the emp_bio table whose salary is between “45000” to “50000”.
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.