How to Use PL/pgSQL function Parameters
In this article we will learn how to use PL/pgSQL function parameters in PostgreSQL.
Basic understanding on PostgreSQL trigger.
Ensure that PostgreSQL server is properly installed, configured and running on the background.
For Linux and Windows systems you can download PostgreSQL here
- To start PostgreSQL server use a LINUX machine use the following command.
- To verify if the service is running use the following command.
- The result should look something like the following:
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)
Aug 01 14:51:20 user-UX330UAK systemd: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
- To start, stop and restart PostgreSQL server in a Windows machine do the following instruction.
- Open Control Panel
- Open Administrative Tools
- Open Services
- Find the PostgreSQL Server service
- Start, Stop or Restart the service
What is PL/pgSQL
PL/pgSQL which also read as Procedural Language/PostgreSQL is supported by PostgreSQL. PL/pgSQL, as a programming language permits more procedural management than that of SQL, this includes the capacity to utilize other control structures and loops. Within SQL statement we can call activity that a trigger performs or functions defined in PL/pgSQL.
PL/pgSQL OUT Parameters
OUT parameters are described as a part of the feature arguments listing and are returned as a component of the result.
OUT parameters were supported since PostgreSQL version 8.1
The syntax to create an OUT parameter is as follows:
We can define
OUT parameters by using the keyword
OUT as shown in the below statement.
OUT highest NUMERIC,
OUT lowest NUMERIC)
highest := GREATEST(num1,num2,num3);
lowest := LEAST(num1,num2,num3);
great_and_least function accepts 5 parameters:
- Three (3)
num1, num2, num3.
- Two (2)
The above function we will obtain the greatest and the least numbers among the three (3)
IN parameters using the built-in functions
We can see that we did not specify
RETURN statement as the
OUT parameter will be the on to return multiple values.
We can use call the
great_and_least function by using the following statement.
As a result we should see something like this.
We can also separate the result in their respective columns by using the following syntax.
As expected the result should look something like the following.
15 | 5
PL/pgSQL IN Parameters
IN parameter is similar to programming that we are able to pass or provide a parameter. we can provide values to a defined stored procedure via these parameters or variables. This pattern of the parameter is a read-only parameter. We are able to put the
IN type parameter value in a variable or make use of it in a query, however, we can not alter its value within the method.
Here is the syntax on how to pass an
IN parameter, see below statement:
param1 IN datatype, param2 IN datatype ... )
We can see in the below sample statement how we can use the above syntax.
RETURNS NUMERIC AS $$
RETURN num1 + num2;
add() function will be accepting two (2) parameters
num2 and will return a
NUMERIC as the two parameters datatype is
NUMERIC as well. We can pass the
IN parameters to the function but not be able to retrieve them back as a component of the result. See the below syntax.
As a result you should see something like this.
PL/pgSQL INOUT Parameters
INOUT parameter is the combination of
OUT parameters. Through the use of
INOUT parameter we are able to put values in a parameter and retrieve a value to the executing application using the identical parameter. This is only possible if the value provided to the function and resulting value is identical datatype. This parameter is utilized if the parameter’s value will be altered in the function.
Below is the syntax for creating
The following syntax shows the
half_num() function that accepts a number and returns the half value of that number.
INOUT a NUMERIC)
a := a * .5 ;
Then we call the function using the following syntax.
The result should look something like the following.
In this article we learn the basic of different types of function parameter including IN, OUT, and INOUT in PostgreSQL.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started