PostgreSQL - Function Parameters
Last Updated :
05 Nov, 2024
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
IN Parameter2. 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:
OUT Parameter3. 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:
INOUT Parameter4. 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.
Similar Reads
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 proc
4 min read
PostgreSQL Date Functions PostgreSQL is widely recognized for its comprehensive support for date and time manipulations, making it an excellent choice for applications requiring precise time management and complex calculations. This article explores the core PostgreSQL date functions, covering how to retrieve the current dat
4 min read
PostgreSQL - Function Overloading In PostgreSQL, it's possible to create multiple functions with the same name, provided that each function has different arguments. This feature, known as function overloading, allows you to define functions that perform similar operations but handle different types or numbers of inputs. PostgreSQL d
3 min read
PostgreSQL - Drop Function In PostgreSQL, the DROP FUNCTION statement is essential for removing functions from your database. Let us learn more about the syntax and the detailed examples to ensure you understand how to use this statement effectively.SyntaxDROP FUNCTION [IF EXISTS] function_name(argument_list) [CASCADE | RESTR
4 min read
PostgreSQL String Functions PostgreSQL is a powerful, open-source relational database management system that offers a rich set of functions and operators for working with string data. String manipulation is an essential task in many applications, and PostgreSQL provides a variety of built-in functions to make working with text
8 min read
PostgreSQL - ASCII Function When working with PostgreSQL, you might need to derive the ASCII (American Standard Code for Information Interchange) code of a character. The PostgreSQL ASCII() function is a handy tool for this purpose. In the case of UTF-8 encoding, the ASCII() function returns the Unicode code point of the chara
2 min read
PostgreSQL - FORMAT Function The PostgreSQL format() function is a powerful tool for string formatting by allowing developers to insert variables into strings using format specifiers like %s, %I, and %L. This function is especially useful for building dynamic SQL queries and ensuring proper formatting of identifiers. It simplif
3 min read
PostgreSQL - INSERT PostgreSQL INSERT statement is one of the fundamental SQL commands used to add new rows to a specified table within a PostgreSQL database. This command allows users to insert data efficiently, whether for a single record or multiple records at once. With the PostgreSQL INSERT INTO clause, we can spe
4 min read
PostgreSQL - User Defined Functions PostgreSQL, one of the most powerful open-source relational database management systems (RDBMS), provides a strong feature set for creating and utilizing user-defined functions (UDFs). By using user-defined functions, we can enhance the modularity, maintainability, and performance of our database ap
5 min read
SQL General Functions SQL general functions are built-in tools provided by SQL databases to perform a variety of operations on data. These functions are crucial for manipulating, analyzing, and transforming data efficiently.In this article, We will learn about the what are the SQL General Functions along with the example
5 min read