PostgreSQL – CREATE FUNCTION Statement
In PostgreSQL CREATE FUNCTION statement to develop user-defined functions.
Syntax:
create [or replace] function function_name(param_list)
returns return_type
language plpgsql
as
$$
declare
-- variable declaration
begin
-- logic
end;
$$
Let’s analyze the above syntax:
- First, specify the name of the function after the create function keywords. If you want to replace the existing function, you can use the replace keywords.
- Then, specify the function parameter list surrounded by parentheses after the function name. A function can have zero or many parameters.
- Next, specify the datatype of the returned value after the returns keyword.
- After that, use the language plpgsql to specify the procedural language of the function. Note that PostgreSQL supports many procedural languages, not just plpgsql.
- Finally, place a block in the dollar-quoted string constant.
For the purpose of example, we will use the sample database ie, dvdrental.
Example:
The following statement creates a function that counts the films whose length between the len_from and len_to parameters:
create function get_film_count(len_from int, len_to int) returns int language plpgsql as $$ declare film_count integer; begin select count(*) into film_count from film where length between len_from and len_to; return film_count; end; $$;
The function get_film_count has two main sections: header and body.
In the Header section:
- First, the name of the function is get_film_count that follows the create function keywords.
- Second, the get_film_count() function accepts two parameters len_from and len_to with the integer datatype.
- Third, the get_film_count function returns an integer specified by the returns int clause
- Finally, the language of the function is plpgsql indicated by the language plpgsql.
In the Body section:
- Use the dollar-quoted string constant syntax that starts with $$ and ends with $$. Between these $$, you can place a block that contains the declaration and logic of the function.
- In the declaration section, declare a variable called film_count that stores the number of films selected from the film table.
- In the body of the block, use the select into statement to select the number of films whose length is between len_from and len_to and assign the result to the film_count variable. At the end of the block, use the return statement to return the film_count.
Now that we got familiar with the structure of the CREATE FUNCTION statement, let’s create a function using the same.
First, launch the psql interactive tool and connect to the dvdrental database and enter the above code in the psql to create the function like this:
create function get_film_count(len_from int, len_to int) returns int language plpgsql as $$ declare film_count integer; begin select count(*) into film_count from film where length between len_from and len_to; return film_count; end; $$;
You will see the following message if the function is created successfully as shown below:
Finally, use the below command to list all user-defined in the current database:
\df
This will show you all the user-defined functions in the database as shown below:
Contact Us