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:

Article Tags :

PostgreSQL

PostgreSQL-function