PostgreSQL - Function Parameters

In PostgreSQL, functions provide an efficient way to encapsulate logic, perform calculations, and handle complex tasks within a database. A thorough understanding of PostgreSQL function parameters is essential for writing flexible and optimized functions.

In this article, we will analyze different types of function parameters, including IN, OUT, INOUT, and VARIADIC parameters, along with examples to illustrate their usage.

What Are Function Parameters in PostgreSQL?

Function parameters in PostgreSQL define how data is passed in and out of a function. Understanding how these parameters work is essential for optimizing function design and enabling flexible functionality. PostgreSQL supports four primary types of function parameters:

• IN Parameter
• OUT Parameter
• INOUT Parameter
• VARIADIC Parameter

1. IN Parameter

The IN parameter is the most common type and is used to pass values into a function. These values are used by the function during execution but cannot be modified or returned by the function. By default, all function parameters in PostgreSQL are of type IN. To better understand these function parameters let’s define a simple function 'get_sum()' that accepts two numbers and returns their sum:

syntax:

CREATE OR REPLACE FUNCTION get_sum(
    a NUMERIC, 
    b NUMERIC) 
RETURNS NUMERIC AS $$
BEGIN
    RETURN a + b;
END; 
$$ LANGUAGE plpgsql;

The 'get_sum()' function accepts two parameters: a, and b,  and returns a numeric. 

The data types of the two parameters are NUMERIC. By default, the parameter’s type of any parameter in PostgreSQL is IN parameter. We can pass the IN parameters to the function but we cannot get them back as a part of the result.

Example

SELECT get_sum(10, 20);

Output

2. Out Parameter

The OUT parameter allows us to return values from a function as part of the result set. When we define an OUT parameter, we don’t need to include a RETURN statement in the function; the values of the OUT parameters are automatically returned. For better understanding let's define a function 'hi_lo()' that takes three numbers and returns the highest and lowest values:

Syntax

CREATE OR REPLACE FUNCTION hi_lo(
    a NUMERIC, 
    b NUMERIC,
    c NUMERIC, 
    OUT hi NUMERIC,
    OUT lo NUMERIC)
AS $$
BEGIN
    hi := GREATEST(a, b, c);
    lo := LEAST(a, b, c);
END; 
$$ LANGUAGE plpgsql;

The 'hi_lo()' function accepts 5 parameters:

• Three IN parameters: a, b, c.
  
• Two OUT parameters: hi (high) and lo (low).

Inside the function, we get the greatest and least numbers of three IN parameters using GREATEST and LEAST built-in functions. Because we use the OUT parameters, we don’t need to have a RETURN statement. The OUT parameters are useful in a function that needs to return multiple values without the need of defining a custom type.

Example

SELECT hi_lo(10, 20, 30);

Output:

3. INOUT Parameter

The INOUT parameter is the combination IN and OUT parameters. It means that the caller can pass a value to the function. The function then changes the argument and passes the value back as a part of the result. The following 'square()' function accepts a number and returns the square of that number.

Syntax:

CREATE OR REPLACE FUNCTION square(
    INOUT a NUMERIC)
AS $$
BEGIN
    a := a * a;
END; 
$$ LANGUAGE plpgsql;

Example

SELECT square(4);

Output:

4. VARIADIC Parameters

The VARIADIC parameter allows a function to accept a variable number of arguments, provided that all arguments are of the same data type. These arguments are passed to the function as an array, which we can then manipulate within the function. Let’s create a function 'sum_avg()' that accepts a list of numbers, calculates their sum and average, and returns both values:

Syntax:

CREATE OR REPLACE FUNCTION sum_avg(
    VARIADIC list NUMERIC[],
    OUT total NUMERIC, 
        OUT average NUMERIC)
AS $$
BEGIN
   SELECT INTO total SUM(list[i])
   FROM generate_subscripts(list, 1) g(i);

   SELECT INTO average AVG(list[i])
   FROM generate_subscripts(list, 1) g(i);
    
END; 
$$ LANGUAGE plpgsql;

Example

The 'sum_avg()' function accepts a list of numbers, calculates the total and average, and returns both values.

SELECT * FROM sum_avg(10, 20, 30);

Output:

Best Practices for Using Function Parameters in PostgreSQL

• Keep IN parameters for inputs that do not need to be modified or returned by the function.
  
• Use OUT parameters when we need to return multiple values without creating a custom type.
  
• Choose INOUT parameters when the input value needs to be modified and returned as part of the function's result.
  
• Utilize VARIADIC parameters for functions that need to handle a flexible number of inputs, ensuring they are of the same type.

Conclusion

Understanding PostgreSQL function parameters IN, OUT, INOUT, and VARIADIC enhances our ability to create efficient, optimized, and flexible database functions. These parameters allow for various ways to pass data into and out of functions, accommodating different types of requirements within PostgreSQL. Whether we are dealing with single inputs, multiple return values, or dynamic lists, PostgreSQL's function parameters provide the tools we need to structure and manage our functions.