VARIADIC parameters in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a function using VARIADIC integer array

Consider the following PostgreSQL function that sums all integers passed as VARIADIC parameters:

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;

What is the output of the query SELECT sum_variadic(1, 2, 3, 4);?

PostgreSQL

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;

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

A 10

B ARRAY[1,2,3,4]

C 1

D ERROR: function sum_variadic(integer, integer, integer, integer) does not exist

💡 Hint

VARIADIC parameters allow passing multiple arguments as an array inside the function.

The function sums all integers passed as VARIADIC parameters. The call passes 1, 2, 3, 4 which sum to 10.

📝 Syntax

intermediate

Identify the correct syntax to declare a VARIADIC parameter

Which of the following is the correct way to declare a VARIADIC parameter in a PostgreSQL function that accepts text arguments?

A CREATE FUNCTION example(args text VARIADIC) RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION example(args VARIADIC text[]) RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION example(VARIADIC args text[]) RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION example(VARIADIC args text) RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;

💡 Hint

VARIADIC must appear before the parameter name and type as an array.

The correct syntax is to write VARIADIC before the parameter name and specify the type as an array, e.g., VARIADIC args text[].

❓ optimization

advanced

Optimizing a function using VARIADIC parameters for concatenation

You have this function that concatenates text arguments passed as VARIADIC parameters:

CREATE OR REPLACE FUNCTION concat_variadic(VARIADIC texts text[]) RETURNS text AS $$
DECLARE
  result text := '';
  t text;
BEGIN
  FOREACH t IN ARRAY texts LOOP
    result := result || t;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Which option provides a more efficient way to concatenate all VARIADIC text arguments?

A Convert texts to JSON and then concatenate the JSON strings.

B Use a nested loop to concatenate each character of each string.

C Use string_agg(texts, '') inside the function.

D Use array_to_string(texts, '') to concatenate all elements at once.

💡 Hint

PostgreSQL has built-in functions to join array elements efficiently.

array_to_string(texts, '') concatenates all array elements efficiently without looping manually.

🧠 Conceptual

advanced

Understanding VARIADIC parameter behavior with arrays

Given this function:

CREATE OR REPLACE FUNCTION test_variadic(VARIADIC vals integer[]) RETURNS integer AS $$
BEGIN
  RETURN array_length(vals, 1);
END;
$$ LANGUAGE plpgsql;

What is the result of the query SELECT test_variadic(ARRAY[1,2,3]);?

PostgreSQL

CREATE OR REPLACE FUNCTION test_variadic(VARIADIC vals integer[]) RETURNS integer AS $$
BEGIN
  RETURN array_length(vals, 1);
END;
$$ LANGUAGE plpgsql;

SELECT test_variadic(ARRAY[1,2,3]);

A 1

B 3

C NULL

D ERROR: function test_variadic(integer[]) does not exist

💡 Hint

Passing an array to a VARIADIC parameter treats the whole array as one argument unless VARIADIC is specified in the call.

Passing ARRAY[1,2,3] as a single argument means vals is an array with one element (the array itself), so length is 1.

🔧 Debug

expert

Diagnose the error when calling a VARIADIC function

You have this function:

CREATE OR REPLACE FUNCTION multiply_variadic(VARIADIC nums integer[]) RETURNS integer AS $$
DECLARE
  product integer := 1;
  n integer;
BEGIN
  FOREACH n IN ARRAY nums LOOP
    product := product * n;
  END LOOP;
  RETURN product;
END;
$$ LANGUAGE plpgsql;

When you run SELECT multiply_variadic(ARRAY[2,3,4]); you get an error:

ERROR: function multiply_variadic(integer[]) does not exist

What is the cause of this error?

A The function does not accept any arguments, so passing an array causes an error.

B The function expects separate integer arguments, but an integer array was passed without VARIADIC keyword.

C The function expects a single integer, but an array was passed.

D The function is missing a RETURNS clause causing the error.

💡 Hint

Check how VARIADIC functions are called with arrays.

To pass an array to a VARIADIC parameter, you must use the VARIADIC keyword in the call: SELECT multiply_variadic(VARIADIC ARRAY[2,3,4]);