What does VARIADIC declarations mean in PostgreSQL?

Question

In PostgreSQL 9.3.4 release note it says:

Ensure that the planner sees equivalent VARIADIC and non-VARIADIC function calls as equivalent (Tom Lane)

I searched the PostgreSQL manual and couldn't find a definition of what it is.

I found that it's realated to the mode of function argument (IN, OUT, VARIADIC) but I didn't understand what it means? When would I want to use it? What does it mean in terms of performance if function has the VARIADIC property?

Pantsuit

Answer 1

Variadic functions are those with an undefined number of arguments, they are present in many programming and query languages.

In the case of PostgreSQL, you can find an example at http://www.postgresql.org/docs/9.1/static/xfunc-sql.html (35.4.5. SQL Functions with Variable Numbers of Arguments):

Effectively, all the actual arguments at or beyond the VARIADIC position are gathered up into a one-dimensional array, as if you had written

Kyne

Can the array be of user defined type? some type that I declare with CREATE TYPE? – Pantsuit

That doesn't answer my question... say I have type A: name text, salary integer... I want the array to be of type A. means every cell of A is a (text,integer) tuple..... [ (text,integer) , (text,integer) ,....] ... can it be done with VARADIC parameter? – Pantsuit

@Pantsuit A variadic parameter must be declared of type array that implies that if you can have user defined typed elements in an array you can also have a variadic parameter of elements of that type. The difference between a variadic parameter and a simple array parameter is that for the former case you can pass the array elements as if its elements were additional parameters in the argument list. That is: Coma separated values. – Nick

Answer 2

When you are not sure about the number of parameters then we use Variadic.

You can refer VARIADIC FUNCTIONS IN POSTGRESQL for details.

See the wiki about Variadic functions:

In computer programming, a variadic function is a function of indefinite arity, i.e., one which accepts a variable number of arguments. Support for variadic functions differs widely among programming languages.

Turenne

Can I use VARIADIC with limit? For example I know it will be 2-5 integer arguments... and if it's more than 5 then it should give me function not exist error. – Pantsuit

@Johnathan:- I am not 100% sure if I understood that correctly but yes you can check the number of parameters and then show message if the count is more than a desired number. – Turenne

Answer 3

A VARIADIC parameter can get an array from the caller.

For example, you use a VARIADIC parameter in a function as shown below. *A VARIADIC parameter must be the last parameter otherwise there is the error:

CREATE FUNCTION my_func(VARIADIC nums INTEGER[]) RETURNS INTEGER
AS $$                -- ↑ Here ↑
BEGIN
  RETURN nums[1] + nums[2];
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func(VARIADIC INTEGER[]) RETURNS INTEGER
AS $$                -- ↑ Here ↑
BEGIN
  RETURN $1[1] + $1[2];
END;
$$ LANGUAGE plpgsql;

Then, calling my_func(2, 3) returns 5 as shown below. You must pass an argument to a VARIADIC parameter otherwise there is the error:

postgres=# SELECT my_func(2, 3);
 my_func
---------
       5
(1 row)

postgres=# SELECT my_func(VARIADIC ARRAY[2,3]);
 my_func
---------
       5
(1 row)

Be careful, calling my_func(ARRAY[2,3]) gets the error as shown below:

postgres=# SELECT my_func(ARRAY[2,3]);
ERROR:  function my_func(integer[]) does not exist
LINE 1: SELECT my_func(ARRAY[2,3]);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit typ
e casts.

And, calling my_func() gets the error as shown below:

postgres=# SELECT my_func();
ERROR:  function my_func() does not exist
LINE 1: SELECT my_func();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type 
casts.
apple=#

Levelheaded