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: