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.
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:
In the above syntax, we have used the following parameters, as shown in the below table:
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:
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:
We have used the following parameters in the Function Body section:
Creating a function in PostgreSQL
In PostgreSQL, we can create a function in two ways:
PostgreSQL Create Function using pgAdmin
We are going to follow the below process to create a function in pgAdmin:
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.
After that, we will open the query tool by clicking on Query tool followed Tools section, as we can see in the below screenshot:
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:
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.
We will enter the below command to create a function as get_car_Price1() in the javatpoint database.
We will get the following output on implementing the above command, which displays that the get_car_Price_count1() function has been created successfully.
We can use the below command to list all user-defined functions in the existing database.
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:
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.
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:
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:
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.
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:
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:
After executing the above command, we will get the below output, which returns those cars whose car_price is between 26000 to 70000.
In the PostgreSQL function section, we have learned the following topics: