PostgreSQL Functions

In this section, we are going to understand the working of the PostgreSQL functions, create function command, and see the real-time example of PostgreSQL CREATE FUNCTION command using the different tools of PostgreSQL such as pgadmin4 and SQL shell (PSQL).

And see the example of calling a user-defined function such as positional notation named notation, the mixed notation.

What is the PostgreSQL Function?

A PostgreSQL function or a stored procedure is a set of SQL and procedural commands such as declarations, assignments, loops, flow-of-control etc. stored on the database server and can be involved using the SQL interface. And it is also known as PostgreSQL stored procedures.

We can create PostgreSQL functions in serval languages, for example, SQL, PL/pgSQL, C, Python etc.

It enables us to perform operations, which would generally take various commands and round trips in a function within the database.

What is the PostgreSQL CREATE Function command?

In PostgreSQL, if we want to specify a new user-defined function, we can use the CREATE FUNCTION command.

Syntax of PostgreSQL CREATE Function command

The Syntax for PostgreSQL CREATE Function command is as follows:

CREATE [OR REPLACE] FUNCTION function_name (arguments)     

RETURNS return_datatype   

LANGUAGE plpgsql  

AS $variable_name$    

DECLARE    

declaration;    

[...] -- variable declaration   

 BEGIN    

< function_body >    

[...]  -- logic  

RETURN { variable_name | value }    

END;   

$$

In the above syntax, we have used the following parameters, as shown in the below table:

Parameters	Description
function_name	The function name parameter is used to define the function name.The function name is written after the CREATE FUNCTION keyword.
[OR REPLACE]	We can use the OR REPLACE keyword if we want to change the current function.And it is an optional parameter.
Function	After using the OR REPLACE keyword, we can define the function parameter list that are covered in the parentheses after the Function Name.And a function can contain zero or several parameters.
RETURN	We can define the data type after the RETURN keyword, which we are going to return from the function.It can be a base, composite, or domain type or reference of the type of a table column.
Language plpgsql	It is used to define the name of the Procedural language in which the function is executed.And not just plpgsql, the PostgreSQL supports various procedural languages.
Function_body	The function_body contains the executable parts of the logics.

Example of PostgreSQL Create Function Command

Let us see a different example to understand the working of the PostgreSQL CREATE Function command.

We are taking the Car table from the Javatpoint database, created in the PostgreSQL Tutorial.

Creating a new function

In the below command, we are creating a new function, which counts the Cars whose Car_Price between the Price_from and Price_to parameters:

Create function get_car_Price(Price_from int, Price_to int)  

returns int  

language plpgsql  

as  

$$  

Declare  

 Car_count integer;  

Begin  

   select count(*)   

   into Car_count  

   from Car  

   where Car_price between Price_from and Price_to;  

   return Car_count;  

End;  

$$;

The get_car_Price function is divided into two main sections, which are Header and Function Body.

We have used the following parameters in the Header section:

• Primarily, we specify the function name as get_car_Price(), which is written after the create function
• After that, the get_car_Price() function contains two parameters Price_from and Price_to, having integer datatype.
• Then, the get_car_Price() function retrieves an integer defined by the return int condition.
• And at the end, we have used the function language as plpgsql.

We have used the following parameters in the Function Body section:

• We have used the dollar-quoted stringconstant illustration in the function section, which starts with $$ and ends with $$.
• In between $$ sign, we can place a block, which covers the declaration and logic of the function.
• In the declaration block, we declared a variable called Car_count,which stores the cars selected from the Car
• In the block section’s body, we have used the SELECT INTOcommand to select the Price of cars whose values are between Price_from and Price_to and give the output to the Car_count
• At the end of the block, we have used the RETURN command to get the

Creating a function in PostgreSQL

In PostgreSQL, we can create a function in two ways:

• PostgreSQL Create Function using pgAdmin
• PostgreSQL Create Function using SQL Shell

PostgreSQL Create Function using pgAdmin

We are going to follow the below process to create a function in pgAdmin:

Step1

Firstly, we will open the latest version pgAdmin in our local system, and we will go to the object tree and connect to the Javatpoint sample database in which we want to create a function.

Step2

After that, we will open the query tool by clicking on Query tool followed Tools section, as we can see in the below screenshot:

Step3

To create the get_car_Price1() function, we will use the above code in the query tool and click the Execute button.

After implementing the above command, we will get the below message window displaying that the function get_car_Price1() has been created successfully into a similar database.

And, we can identify the function get_car_Price() in the Functions list as we can see in the following screenshot:

Note: If we cannot identify the function name, we can right-click the Functions node and select Refresh… menu item to revive the function list:

Creating a function using SQL Shell(psql)

We are going to follow the below process to create a table in psql:

Step1

• Firstly, we will open the psqlin our local system, and we will connect to the database where we want to create a function.
• We will create a table in the javatpointdatabase, which we created earlier in the PostgreSQL tutorial.

Step2

• For connecting a database, we will enter the below command:

\c javatpoint  

Output

After executing the above command, we will get the following output:

Note: If we enter a similar command as above to create a function, in psql, it will issue the below error that is: the function get_car_price already exists with the same argument types.

Therefore, to resolve this error, we create a new function as get_car_Price1() in the next step.

Step3

We will enter the below command to create a function as get_car_Price1() in the javatpoint database.

javatpoint=# Create function get_car_Price1(Price_from int, Price_to int)  

javatpoint-# returns int  

javatpoint-# language plpgsql  

javatpoint-# as  

javatpoint-# $$  

javatpoint$# Declare  

javatpoint$#  Car_count integer;  

javatpoint$# Begin  

javatpoint$#    select count(*)  

javatpoint$#    into Car_count  

javatpoint$#    from Car  

javatpoint$#    where car_price between Price_from and Price_to;  

javatpoint$#    return Price_count;  

javatpoint$# End;  

javatpoint$# $$;

Output

We will get the following output on implementing the above command, which displays that the get_car_Price_count1() function has been created successfully.

Step4

We can use the below command to list all user-defined functions in the existing database.

javatpoint=# \df  

Output

After executing the above command, we will get the below output:

How to Call a user-defined function

In PostgreSQL, we can call the user-defined function in three ways, which are as follows:

• Positional notation
• Named notation
• The mixed notation

Calling a function using positional notation

If we want to describe the arguments in a similar order as parameters, we can call a function with the positional notation’s help.

Let us see a sample example to understand the Positional Notation working to call a particular function.

In the below example, the get_car_price() arguments are 26000 and 70000, which is equivalent to the Price_from and Price_to parameters.

Select get_car_Price(26000,70000);  

Output

We will get the following output on implementing the above command, which retrieves those four rows whose car_price is between 26000 to 70000.

When the function hardly having any parameters, we can call a function with the help of the positional notation.

If the function contains several parameters, we can use the named notation to call the particular function because using the named notation will make the function call more understandable.

Calling a function using named notation

In the below example, we are displaying the working of calling the get_car_Price() function using the named notation:

select get_car_Price(  

 Price_from => 26000,   

    Price_to => 70000  

);

Output

We will get the following output on executing the above command, which displays four rows based on the above range of car_price.

In the notation mentioned earlier, we have used the => to distinguish the argument’s name and value.

PostgreSQL allows the older syntax created on := for the backward compatibility, as we can see in the following command:

select get_car_Price(  

 Price_from := 26000,   

     Price_to := 70000  

);

Output

After executing the above command, we will get a similar output as compared to the above command’s result where we use the ‘=>’ instead of ‘:=’.

Calling a function using the mixed notation

It is the grouping of positional and named notations.

Let us see a sample example to understand the working of the Calling a function using mixed notation.

In the mixed notation, we cannot use the named Parameters before positional Parameters.

For example:

In the below command, we will use the named notion for Price_from parameter as Price_from=>26000, whereas for the Price_to parameter, we have used the positional notion as 70000, as we can see in the below command:

select get_car_Price(Price_from=>26000,70000);  

Output

After executing the above command, PostgreSQL raises an error, which says that the positional argument cannot follow the named argument.

To resolve the above error, we are using the positional and named notation for the get_car_price() function where the 26000 is used to represent the Positional notation; on the other hand, Price_to=>70000 is used to represent the named notation:

select get_car_Price(26000,Price_to=>70000);  

Output

After executing the above command, we will get the below output, which returns those cars whose car_price is between 26000 to 70000.

Overview

In the PostgreSQL function section, we have learned the following topics:

• We have used the CREATE Functioncommand to create a user-defined function for the particular table.
• We have understood the process of calling a user-defined function with the help of different notations such as Positional, Named and Mixed.