SQL VARIADIC

What is SQL VARIADIC?

VARIADIC lets a function accept or be passed a variable-length list of arguments that is treated as a one-dimensional array.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL VARIADIC: Supported: PostgreSQL, Amazon Redshift, Greenplum, CockroachDB, YugabyteDB, TimescaleDB. Not supported: MySQL, SQL Server, Oracle, SQLite, Snowflake.

SQL VARIADIC Full Explanation

In PostgreSQL, VARIADIC can appear in two places: (1) in CREATE FUNCTION to declare the last parameter as a variable-length array, and (2) in a function call to expand an existing array into individual arguments. When declared, any number of positional arguments supplied after the fixed parameters are automatically packed into a single array that the function receives. When used in a call, an array expression is unpacked so each element is passed as a separate argument. Only the final parameter of a function may be marked VARIADIC, and it must be a one-dimensional array type (e.g., text[]). VARIADIC parameters cannot be declared OUT or INOUT. Calls may omit the VARIADIC keyword and pass a plain list, or include it and supply an explicit array. Mixing named notation and VARIADIC is unsupported. This feature simplifies writing utility functions such as custom aggregators, comparison helpers (greatest, least), or dynamic SQL builders.

SQL VARIADIC Syntax

-- In a function definition
CREATE FUNCTION func_name(fixed_arg type, VARIADIC vararg elem_type[]) RETURNS return_type
LANGUAGE plpgsql AS $$
BEGIN
  -- function body
END;
$$;

-- In a function call with individual arguments packed implicitly
SELECT func_name(fixed_value, 'a', 'b', 'c');

-- In a function call with an existing array unpacked explicitly
SELECT func_name(fixed_value, VARIADIC ARRAY['a','b','c']);

SQL VARIADIC Parameters

func_name (identifier) - Name of the function being defined or called
fixed_arg (any type) - Regular parameter(s) that precede the variadic one
vararg (array type) - One-dimensional array that will collect or expand elements
elem_type (base type) - Element type of the array (text, int, numeric, etc.)
array_expression (array) - Array supplied in a call when using VARIADIC keyword

Example Queries Using SQL VARIADIC

-- 1. Define a function that concatenates any number of text values
CREATE FUNCTION concat_texts(VARIADIC parts text[]) RETURNS text
LANGUAGE sql AS $$
 SELECT string_agg(p, '') FROM unnest(parts) AS p;
$$;

-- 2. Call with a varying number of arguments (packed automatically)
SELECT concat_texts('Hello', ' ', 'World', '!');
-- 3. Call by expanding an existing array (unpacked explicitly)
SELECT concat_texts(VARIADIC ARRAY['A','B','C']);

-- 4. Use built-in GREATEST with VARIADIC to compare an array of numbers
SELECT GREATEST(VARIADIC ARRAY[5, 10, 3]);

Expected Output Using SQL VARIADIC

concat_texts returns the single string 'Hello World!'.
Second call returns 'ABC'.
GREATEST returns 10 because that is the maximum element of the array.

Use Cases with SQL VARIADIC

Write helper functions that accept any number of columns or literals without overloading many arities.
Pass arrays into generic comparison functions (GREATEST, LEAST) for cleaner code.
Build SQL generators where arguments are optional or user-defined.
Simplify wrapper functions that forward parameters to lower-level functions.

Common Mistakes with SQL VARIADIC

Marking a non-last parameter as VARIADIC (PostgreSQL rejects this).
Forgetting that VARIADIC requires a one-dimensional array type.
Mixing named notation with VARIADIC in the same call.
Passing an array without the VARIADIC keyword, leading to a nested array instead of expansion.

First Introduced In

PostgreSQL 8.4

Frequently Asked Questions

What does VARIADIC do in PostgreSQL?

VARIADIC marks the last function parameter as a catch-all array, allowing the function to accept any number of additional arguments without defining multiple overloads.

Can I declare more than one VARIADIC parameter?

No. PostgreSQL enforces that only the final parameter may be VARIADIC. Attempting to mark earlier parameters results in an error.

Do I have to include VARIADIC in the call?

Not necessarily. Supplying a list of values after the fixed parameters automatically packs them into the array. You use the keyword when you want to expand an existing array into separate arguments.

Is VARIADIC portable to other databases?

VARIADIC is PostgreSQL-specific. Databases like MySQL, SQL Server, Oracle, and SQLite lack direct support, so code using it is not portable without refactoring.