How to Use Variadic Parameter in Function in PostgreSQL

In PostgreSQL, you can use the VARIADIC keyword in a function parameter to accept a variable number of arguments as an array. Define the parameter as an array type and mark it with VARIADIC to allow calling the function with any number of arguments.

Syntax

To declare a variadic parameter in a PostgreSQL function, use the VARIADIC keyword before an array-type parameter. This allows the function to accept zero or more arguments of the array element type.

• VARIADIC param_name type[]: The parameter that collects all extra arguments as an array.
• The function can then process the array inside its body.

sql

CREATE FUNCTION function_name(VARIADIC param_name type[]) RETURNS return_type AS $$
BEGIN
  -- function logic using param_name as an array
END;
$$ LANGUAGE plpgsql;

Example

This example shows a function that sums any number of integer inputs using a variadic parameter.

sql

CREATE OR REPLACE FUNCTION sum_variadic(VARIADIC nums integer[]) RETURNS integer AS $$
DECLARE
  total integer := 0;
  n integer;
BEGIN
  FOREACH n IN ARRAY nums LOOP
    total := total + n;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

-- Calling the function with multiple arguments
SELECT sum_variadic(1, 2, 3, 4, 5) AS total_sum;

Output

 total_sum 
-----------
        15
(1 row)

Common Pitfalls

Common mistakes when using variadic parameters include:

• Not declaring the parameter as an array type with VARIADIC.
• Trying to pass an array without VARIADIC keyword when calling the function.
• Confusing variadic parameters with regular array parameters (variadic allows flexible argument count).

Example of wrong and right call:

sql

-- Wrong: passing array without VARIADIC keyword
SELECT sum_variadic(ARRAY[1,2,3]); -- This passes one argument (an array), not multiple integers

-- Right: passing array with VARIADIC keyword
SELECT sum_variadic(VARIADIC ARRAY[1,2,3]); -- This expands array elements as separate arguments

Output

 sum_variadic 
-------------
           6
(1 row)

Quick Reference

• Define variadic parameter: Use VARIADIC param_name type[] in function signature.
• Call with multiple arguments: Pass arguments normally, e.g. func(1,2,3).
• Call with array: Use VARIADIC keyword before array, e.g. func(VARIADIC ARRAY[1,2,3]).
• Inside function: The parameter is an array you can loop over.

Key Takeaways

Use the VARIADIC keyword before an array parameter to accept variable arguments in PostgreSQL functions.

Call variadic functions with multiple arguments directly or use VARIADIC keyword when passing an array.

Inside the function, the variadic parameter behaves like a normal array you can iterate over.

Without VARIADIC, passing an array is treated as a single argument, not multiple arguments.

Variadic parameters must be the last parameter in the function signature.