August 4, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL
CREATE FUNCTION statement to develop user-defined functions.
Introduction to Create Function statement
create function statement allows you to define a new user-defined function.
The following illustrates the syntax of the
create function statement:
create [or replace] function function_name(param_list) returns return_type language plpgsql as $$ declare -- variable declaration begin -- logic end; $$
In this syntax:
- First, specify the name of the function after the
create functionkeywords. If you want to replace the existing function, you can use the
- 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
- After that, use the
language plpgsqlto specify the procedural language of the function. Note that PostgreSQL supports many procedural languages, not just
- Finally, place a block in the dollar-quoted string constant.
PostgreSQL Create Function statement examples
We’ll use the film table from the dvdrental sample database.
The following statement creates a function that counts the films whose length between the
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; $$;
get_film_count has two main sections: header and body.
In the header section:
- First, the name of the function is
get_film_countthat follows the
- Second, the
get_film_count()function accepts two parameters len_from and len_to with the integer datatype.
- Third, the
get_film_countfunction returns an integer specified by the
- Finally, the language of the function is
plpgsqlindicated by the
In the function body:
- 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_countthat stores the number of films selected from the
- In the body of the block, use the
select intostatement to select the number of films whose length are between
len_toand assign the result to the
film_countvariable. At the end of the block, use the
returnstatement to return the
To execute the create function statement, you can use any PostgreSQL client tool including psql and pgAdmin
1) Creating a function using pgAdmin
First, launch the pgAdmin tool and connect to the dvdrental sample database.
Second, open the query tool by selecting Tools > Query Tool.
Third, enter the above code int the query tool and click the Execute button to create the
If everything is fine, you will see the following message:
CREATE FUNCTION Query returned successfully in 44 msec.
It means that the function
get_film_count is created successfully.
Finnally, you can find the function
get_film_count in the Functions list:
In case you could not find the function name, you can right-click the Functions node and select Refresh… menu item to refresh the function list.
2) Creating a function using psql
First, launch the psql interactive tool and connect to the
Second, enter the above code in the psql to create the function like this:
dvdrental=# create function get_film_count(len_from int, len_to int) dvdrental-# returns int dvdrental-# language plpgsql dvdrental-# as dvdrental-# $$ dvdrental$# declare dvdrental$# film_count integer; dvdrental$# begin dvdrental$# select count(*) dvdrental$# into film_count dvdrental$# from film dvdrental$# where length between len_from and len_to; dvdrental$# dvdrental$# return film_count; dvdrental$# end; dvdrental$# $$;
You will see the following message if the function is created successfully:
Third, use the
\df command to list all user-defined in the current database:
Calling a user-defined function
PostgreSQL provides you with three ways to call a user-defined function:
- Using positional notation
- Using named notation
- Using the mixed notation.
1) Using positional notation
To call a function using the positional notation, you need to specify the arguments in the same order as parameters. For example:
get_film_count ---------------- 325 (1 row)
In this example, the arguments of the get_film_count() are
90 that corresponding to the
You call a function using the positional notation when the function has few parameters.
If the function has many parameters, you should call it using the named notation since it will make the function call more obvious.
2) Using named notation
The following shows how to call the
get_film_count function using the positional notation:
select get_film_count( len_from => 40, len_to => 90 );
get_film_count ---------------- 325 (1 row)
In the named notation, you use the
=> to separate the argument’s name and its value.
For backward compatibility, PostgreSQL supports the older syntax based on
:= as follows:
select get_film_count( len_from := 40, len_to := 90 );
3) Using mixed notation
The mixed notation is the combination of positional and named notations. For example:
select get_film_count(40, len_to => 90);
Note that you cannot use the named arguments before positional arguments like this:
select get_film_count(len_from => 40, 90);
ERROR: positional argument cannot follow named argument LINE 1: select get_film_count(len_from => 40, 90);
In this tutorial, you have learned how to use the
CREATE FUNCTION statement to create a user-defined function.