PostgreSQL - Function Parameter Modes

Last Updated : 13 Aug, 2024

PostgreSQL provides powerful features that allow developers to pass data between a calling program and a procedure or function. This is achieved through the use of parameters, which are essential for flexible and dynamic database operations. Parameters enable you to supply data to a function or procedure and receive data back, making your database interactions more efficient and versatile.

What are Parameters?

Parameters in PostgreSQL functions and procedures are variables that pass data from the calling program to the function or procedure. There are two types of parameters:

Formal Parameters: These are the parameters declared in the function or procedure definition. They define what kind of data the function or procedure will accept and return.
Actual Parameters: These are the actual values or variables the calling program passes when invoking the function or procedure.

Parameter Modes in PostgreSQL

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.

1. The IN Mode

Purpose: The IN mode is used to pass a value to the function or procedure.
Behavior: Parameters in IN mode act like constants; they cannot be assigned a new value within the function or procedure.
Default Mode: If no mode is specified, PostgreSQL assumes the parameter is in IN mode.

2. The OUT Mode:

Purpose: The OUT mode is used to return a value from the function or procedure.
Behavior: Parameters in OUT mode act like uninitialized variables that must be assigned a value during the function's execution.
Usage: This mode is particularly useful for functions that need to return multiple values.

3. THE INOUT MODE:

Purpose: The INOUT mode is a combination of IN and OUT modes.
Behavior: Parameters in INOUT mode are initialized with a value passed by the caller and return a possibly modified value after the function's execution. These parameters act like initialized variables.

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

PostgreSQL Function Parameter Modes Examples

Let us look at some of the examples of Function Parameter Modes in PostgreSQL to better understand the concept. For testing, we create a sample table using the below commands to perform examples:

PostgreSQL

CREATE TABLE employees (
   employee_id serial PRIMARY KEY,
   full_name VARCHAR NOT NULL,
   manager_id INT
);
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: Using IN, OUT and INOUT Parameters

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.

Query:

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 employee_id = eid
    AND manager_id = mid;
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:

PostgreSQL Function Parameter Modes Example

Explanation:

IN: 'eid' is an IN parameter because it is only used to pass the employee's ID to the function.
OUT: 'name' is an OUT parameter because the function will return the employee's name.
INOUT: 'mid' is an INOUT parameter because it is both used as an input and returned as an output.

Example 2: Counting Employees Using an OUT Parameter

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

Query:

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:

Explanation: The function will return the total count of employees in the table.

Example 3: Swapping Values Using INOUT Parameters

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.

Explanation: The function will return the swapped values of 'x' and 'y'.

PostgreSQL - Function Parameter Modes

ashutoshrathi

Improve

Article Tags :

PostgreSQL - Function Parameter Modes

What are Parameters?

Parameter Modes in PostgreSQL

1. The IN Mode

2. The OUT Mode:

3. THE INOUT MODE:

PostgreSQL Function Parameter Modes Examples

Example 1: Using IN, OUT and INOUT Parameters

Example 2: Counting Employees Using an OUT Parameter

Example 3: Swapping Values Using INOUT Parameters

Similar Reads