PostgreSQL – Function Parameter Modes

While using procedures and functions, PostgreSQL allows us to pass data from the calling program to the procedure or function and to receive data back from the procedure or function. This functionality is achieved using Parameters.

Parameters declared in the function definition are known as Formal parameters. When the procedure or function is invoked, the calling program supplies the data that is to be used in the called program’s processing as well as the variables that receive the results of the processing from the called program. The data and variables supplied by the calling program when the procedure or function are known as Actual parameters.

A Parameter mode is used to decide the behavior of the parameters. PL/pgSQL supports three parameter modes: in, out, and inout. By default, a parameter uses the IN mode. 

The IN Mode:

The IN mode is used to pass a value to the function. IN parameters simply act like constants. The IN parameters cannot be assigned any value. The IN mode is the default mode which is assigned if not specified explicitly.

The OUT Mode:

The OUT parameters are declared as a part of the argument list and are returned as a part of the result. The OUT parameters are very useful in functions that require returning multiple values. They act like uninitialized variables.
Unlike the IN parameters, a value must be assigned to the OUT parameters.

THE INOUT MODE:

The INOUT mode is simply a combination of both IN and OUT modes. In the INOUT Mode, the caller passes an argument to the procedure or function. During the function processing, the value of the argument changes, and the same value is returned by the function. Thus, the INOUT parameters act like initialized variables, and they should be assigned a value during function processing.

The following table illustrates the three parameter mode:

IN	OUT	INOUT
Taken by default	Need to be specified explicitly	Need to be specified explicitly
Values are passed to a function	Value is returned by a function	A value is passed to the function which returns another updated value
Behaves like constants	Behaves like an uninitialized variable	Behaves like an initialized variable
Values cannot be assigned	Must assign a value	Should assign a value

For testing, we create a sample table using the below commands to perform examples:

CREATE TABLE employees (

   employee_id serial PRIMARY KEY,

   full_name VARCHAR NOT NULL,

   manager_id INT

);

Then we insert data into our employee table as follows:

INSERT INTO employees (
   employee_id,
   full_name,
   manager_id
)
VALUES
   (1, 'M.S Dhoni', NULL),
   (2, 'Sachin Tendulkar', 1),
   (3, 'R. Sharma', 1),
   (4, 'S. Raina', 1),
   (5, 'B. Kumar', 1),
   (6, 'Y. Singh', 2),
   (7, 'Virender Sehwag ', 2),
   (8, 'Ajinkya Rahane', 2),
   (9, 'Shikhar Dhawan', 2),
   (10, 'Mohammed Shami', 3),
   (11, 'Shreyas Iyer', 3),
   (12, 'Mayank Agarwal', 3),
   (13, 'K. L. Rahul', 3),
   (14, 'Hardik Pandya', 4),
   (15, 'Dinesh Karthik', 4),
   (16, 'Jasprit Bumrah', 7),
   (17, 'Kuldeep Yadav', 7),
   (18, 'Yuzvendra Chahal', 8),
   (19, 'Rishabh Pant', 8),
   (20, 'Sanju Samson', 8);

The table is:

Example 1:

The following function is created to find the name of the employee along with its manager id given the employee id and its manager id. 

create or replace function get_stats(
  in eid int,
  out name varchar,
  inout mid int)
language plpgsql
as $$
begin
  select full_name,manager_id
  into name,mid
  from employees
  where manager_id=mid
  and
  employee_id=eid;
end; $$;

To call this function, we need to provide employee id and then manager id. As an example,

select * from get_stats(2,1);

Output:

In this example, we have taken the employee name as OUT as we need to output the name, employee id as IN as it is only required as input, and manager id as INOUT as it is both required as an input as well as an output. 

Example 2:

The following function is used to get the count of employees in the employee table.

create or replace function empcount(
  out ecount int)
language plpgsql
as $$
begin
  select count(*)
  into ecount
  from employees;
end; $$;

To call this function :

select * from empcount();

Output:

Example 3:

The following function is used to swap the contents of 2 variables

create or replace function swapper(
  inout x int,
  inout y int)
language plpgsql
as $$
begin
  select x,y into y,x;
end; $$;

To call this function :

select * from swapper(198,457);

Output:

As we need to change the values and then display the changed values, we use INOUT mode.