PostgreSQL – Function Parameter Modes

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:

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:

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:

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’.

A

ashutoshrathi

Follow

Improve

Article Tags :

• PostgreSQL
• Technical Scripter
• PostgreSQL-function
• Technical Scripter 2020