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.
sudo service postgresql start
  • To verify if the service is running use the following command.
service postgresql status
  • The result should look something like the following:
● 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.
  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Services
  4. Find the PostgreSQL Server service
  5. Start, Stop or Restart the service

Restarting PostgreSQL server in a Windows Machine

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:

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.

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) IN parameters num1, num2, num3.
  • Two (2) OUT parameters great and least.

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.

SELECT great_and_least(5,10,15);

As a result we should see something like this.

 great_and_least
-----------------
 (15,5)
(1 ROW)

We can also separate the result in their respective columns by using the following syntax.

SELECT * FROM great_and_least(5,10,15);

As expected the result should look something like the following.

 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:

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.

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.

SELECT ADD(3,6);

As a result you should see something like this.

 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.

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.

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.

SELECT half_num(6);

The result should look something like the following.

 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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.