VARIADIC parameters in PostgreSQL

Introduction

VARIADIC parameters let you pass a flexible number of arguments to a function. This helps when you don't know how many inputs you will get.

When you want a function to accept any number of values, like adding many numbers.

When you want to search for multiple keywords in a text without fixing how many keywords.

When you want to collect a list of items in one call without making many calls.

When you want to simplify your code by handling multiple inputs in one function.

Syntax

PostgreSQL

CREATE FUNCTION function_name(VARIADIC param_name data_type[]) RETURNS return_type AS $$
BEGIN
  -- function body
END;
$$ LANGUAGE plpgsql;

The VARIADIC keyword must be used before the last parameter in the function.

The parameter with VARIADIC must be an array type.

Examples

This function sums any number of integers passed as arguments.

PostgreSQL

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

Calls the sum_numbers function with 4 numbers, returns 10.

PostgreSQL

SELECT sum_numbers(1, 2, 3, 4);

This function joins multiple text inputs into one string separated by commas.

PostgreSQL

CREATE FUNCTION concat_texts(VARIADIC texts text[]) RETURNS text AS $$
BEGIN
  RETURN array_to_string(texts, ', ');
END;
$$ LANGUAGE plpgsql;

Returns 'apple, banana, cherry'.

PostgreSQL

SELECT concat_texts('apple', 'banana', 'cherry');

Sample Program

This function greets each name passed to it. If no names are given, it says 'Hello, nobody!'.

PostgreSQL

CREATE FUNCTION greet_people(VARIADIC names text[]) RETURNS text AS $$
DECLARE
  result text := '';
  name text;
BEGIN
  IF array_length(names, 1) IS NULL THEN
    RETURN 'Hello, nobody!';
  END IF;
  FOREACH name IN ARRAY names LOOP
    result := result || 'Hello, ' || name || '! '; 
  END LOOP;
  RETURN trim(result);
END;
$$ LANGUAGE plpgsql;

SELECT greet_people('Alice', 'Bob', 'Charlie');

Important Notes

You can call a VARIADIC function with an array by using VARIADIC keyword in the call, like: SELECT func(VARIADIC array);

VARIADIC parameters must be the last parameter in the function definition.

Summary

VARIADIC lets functions accept flexible numbers of arguments as an array.

Use VARIADIC for cleaner code when input count varies.

Remember VARIADIC parameter must be last and is always an array type.