How to Use PL/pgSQL function Parameters
Introduction
In this article we will learn how to use PL/pgSQL function parameters in PostgreSQL.
Prerequisites
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.
1 | sudo service postgresql start |
- To verify if the service is running use the following command.
1 | service postgresql status |
- The result should look something like the following:
1 2 3 4 5 6 7 8 9 | ● postgresql.service - PostgreSQL RDBMS 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[1]: Starting PostgreSQL RDBMS... Aug 01 14:51:20 user-UX330UAK systemd[1]: 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
The OUT parameters are described as a part of the feature arguments listing and are returned as a component of the result.
NOTE: The OUT parameters were supported since PostgreSQL version 8.1
The syntax to create an OUT parameter is as follows:
1 | CREATE [OR REPLACE] FUNCTION function_name (param_name OUT datatype) |
We can define OUT parameters by using the keyword OUT as shown in the below statement.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE OR REPLACE FUNCTION great_and_least( num1 NUMERIC, num2 NUMERIC, num3 NUMERIC, OUT highest NUMERIC, OUT lowest NUMERIC) AS $$ BEGIN highest := GREATEST(num1,num2,num3); lowest := LEAST(num1,num2,num3); END; $$ LANGUAGE plpgsql; |
The Above great_and_least function accepts 5 parameters:
- Three (3)
INparametersnum1, num2, num3. - Two (2)
OUTparametersgreatandleast.
The above function we will obtain the greatest and the least numbers among the three (3) IN parameters using the built-in functions GREATEST and LEAST.
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.
1 | SELECT great_and_least(5,10,15); |
As a result we should see something like this.
1 2 3 4 | great_and_least ----------------- (15,5) (1 ROW) |
We can also separate the result in their respective columns by using the following syntax.
1 | SELECT * FROM great_and_least(5,10,15); |
As expected the result should look something like the following.
1 2 3 4 | highest | lowest ---------+-------- 15 | 5 (1 ROW) |
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:
1 2 | CREATE [OR REPLACE] FUNCTION function_name ( param1 IN datatype, param2 IN datatype ... ) |
We can see in the below sample statement how we can use the above syntax.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE FUNCTION ADD( num1 NUMERIC, num2 NUMERIC) RETURNS NUMERIC AS $$ BEGIN RETURN num1 + num2; END; $$ LANGUAGE plpgsql; |
The add() function will be accepting two (2) parameters num1 and 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.
1 | SELECT ADD(3,6); |
As a result you should see something like this.
1 2 3 4 | ADD ----- 9 (1 ROW) |
PL/pgSQL INOUT Parameters
The INOUT parameter is the combination of IN and 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 INOUT parameter.
1 | CREATE [OR REPLACE] FUNCTION function_name (param_name INOUT datatype) |
The following syntax shows the half_num() function that accepts a number and returns the half value of that number.
1 2 3 4 5 6 7 | CREATE OR REPLACE FUNCTION half_num( INOUT a NUMERIC) AS $$ BEGIN a := a * .5 ; END; $$ LANGUAGE plpgsql; |
Then we call the function using the following syntax.
1 | SELECT half_num(6); |
The result should look something like the following.
1 2 3 4 | half_num ---------- 3.0 (1 ROW) |
Conclusion
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