VARIADIC parameters in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a function that accepts a variadic integer array parameter.

PostgreSQL

CREATE FUNCTION sum_numbers([1] integer[]) RETURNS integer AS $$ BEGIN RETURN 0; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

Anumbers VARIADIC

Bnumbers

CVARIADIC numbers

DVARIADIC

Attempts:

3 left

💡 Hint

Common Mistakes

Placing VARIADIC after the parameter name.

Omitting the VARIADIC keyword.

Using VARIADIC without a parameter name.

In PostgreSQL, to declare a variadic parameter, you write VARIADIC before the parameter name and type, like VARIADIC numbers integer[].

2fill in blank

medium

Complete the function call to pass multiple integers as a variadic argument.

PostgreSQL

SELECT sum_numbers([1]);

Drag options to blanks, or click blank then click option'

AVARIADIC ARRAY[1, 2, 3]

BARRAY[1, 2, 3]

C1, 2, 3

DVARIADIC 1, 2, 3

Attempts:

3 left

💡 Hint

Common Mistakes

Passing values without VARIADIC keyword.

Passing values separated by commas without ARRAY.

Using VARIADIC without ARRAY.

When calling a function with a variadic parameter, you use VARIADIC ARRAY[...] to pass multiple values as one array.

3fill in blank

hard

Fix the error in the function definition to correctly use a variadic text parameter.

PostgreSQL

CREATE FUNCTION concat_texts([1] text) RETURNS text AS $$ BEGIN RETURN ''; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

AVARIADIC texts

BVARIADIC texts text[]

Ctexts VARIADIC

Dtexts text VARIADIC

Attempts:

3 left

💡 Hint

Common Mistakes

Placing VARIADIC after the parameter name.

Using text[] instead of text as type.

Omitting VARIADIC keyword.

The correct syntax is VARIADIC texts text to declare a variadic parameter of type text.

4fill in blank

hard

Fill both blanks to create a function that sums variadic integers and returns the total.

PostgreSQL

CREATE FUNCTION sum_all([1] integer) RETURNS integer AS $$ DECLARE total integer := 0; BEGIN FOREACH num IN ARRAY [2] LOOP total := total + num; END LOOP; RETURN total; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

AVARIADIC nums

Bnums

Attempts:

3 left

💡 Hint

Common Mistakes

Not using VARIADIC in the parameter.

Looping over the wrong variable name.

Using the parameter as a scalar instead of an array.

The function parameter must be declared as VARIADIC nums integer (here VARIADIC nums is the blank), and inside the function, you loop over nums which is the array of integers.

5fill in blank

hard

Fill all three blanks to define and call a variadic function that concatenates texts with a separator.

PostgreSQL

CREATE FUNCTION concat_with_sep([1] text, [2] text) RETURNS text AS $$ BEGIN RETURN array_to_string([3], sep); END; $$ LANGUAGE plpgsql; SELECT concat_with_sep(',', VARIADIC ARRAY['a', 'b', 'c']);

Drag options to blanks, or click blank then click option'

Asep

BVARIADIC texts

Ctexts

Dtexts[]

Attempts:

3 left

💡 Hint

Common Mistakes

Not using VARIADIC for the second parameter.

Using the wrong variable name inside the function.

Declaring the separator as variadic.

The first parameter is the separator sep, the second is the variadic parameter VARIADIC texts, and inside the function, you use texts as the array to join.

Practice

1. What does the VARIADIC keyword do in a PostgreSQL function?

easy

A. Specifies that the function is a trigger

B. Defines a function that returns multiple result sets

C. Allows the function to accept a variable number of arguments as an array

D. Indicates the function is immutable

Solution

1. Step 1: Understand the role of VARIADIC

   VARIADIC allows a function to accept any number of arguments as a single array parameter.

2. Step 2: Compare with other options

   Options B, C, and D describe unrelated function features.

3. Final Answer:

   Allows the function to accept a variable number of arguments as an array -> Option C

4. Quick Check:

   VARIADIC = variable arguments as array [OK]

Hint: VARIADIC means flexible argument count packed as array [OK]

Common Mistakes:

• Thinking VARIADIC returns multiple result sets
• Confusing VARIADIC with triggers
• Assuming VARIADIC changes function volatility

2. Which of the following is the correct syntax to declare a VARIADIC parameter in a PostgreSQL function?

easy

A. CREATE FUNCTION example(VARIADIC nums int[]) RETURNS int AS $$ ... $$ LANGUAGE sql;

B. CREATE FUNCTION example(nums VARIADIC int) RETURNS int AS $$ ... $$ LANGUAGE sql;

C. CREATE FUNCTION example(nums int VARIADIC) RETURNS int AS $$ ... $$ LANGUAGE sql;

D. CREATE FUNCTION example(nums int[]) VARIADIC RETURNS int AS $$ ... $$ LANGUAGE sql;

Solution

1. Step 1: Identify correct VARIADIC syntax

   VARIADIC must precede the parameter name and the parameter type must be an array type, e.g., VARIADIC nums int[].

2. Step 2: Check each option

   CREATE FUNCTION example(VARIADIC nums int[]) RETURNS int AS $$ ... $$ LANGUAGE sql; correctly places VARIADIC before the parameter name and uses an array type. Options A, B, and C misuse the position or syntax of VARIADIC.

3. Final Answer:

   CREATE FUNCTION example(VARIADIC nums int[]) RETURNS int AS $$ ... $$ LANGUAGE sql; -> Option A

4. Quick Check:

   VARIADIC before param name + array type = correct syntax [OK]

Hint: VARIADIC goes before param name and uses array type [OK]

Common Mistakes:

• Placing VARIADIC after parameter name
• Using non-array type with VARIADIC
• Putting VARIADIC after RETURNS keyword

3. Given the function:

CREATE FUNCTION sum_all(VARIADIC nums int[]) RETURNS int AS $$
  SELECT SUM(n) FROM unnest(nums) AS n;
$$ LANGUAGE sql;

What is the result of SELECT sum_all(2, 4, 6);?

medium

A. ARRAY[2,4,6]

B. 12

C. Error: wrong number of arguments

D. NULL

Solution

1. Step 1: Understand VARIADIC input

   The function accepts any number of integers as separate arguments, packed into an array named nums.

2. Step 2: Calculate the sum

   Input arguments 2, 4, 6 become array [2,4,6]. The function sums these values: 2 + 4 + 6 = 12.

3. Final Answer:

   12 -> Option B

4. Quick Check:

   Sum of 2,4,6 = 12 [OK]

Hint: VARIADIC packs inputs as array; sum them inside function [OK]

Common Mistakes:

• Expecting an array output instead of sum
• Thinking VARIADIC requires explicit array input
• Assuming function errors on multiple arguments

4. Consider this function definition:

CREATE FUNCTION concat_texts(VARIADIC texts text) RETURNS text AS $$
  SELECT string_agg(t, ',') FROM unnest(texts) AS t;
$$ LANGUAGE sql;

Which error will occur if you call SELECT concat_texts('hello', 'world');?

medium

A. Error: VARIADIC parameter must be declared as an array type

B. No error, returns 'hello,world'

C. Error: missing FROM-clause entry for table 'texts'

D. Error: function does not exist

Solution

1. Step 1: Check VARIADIC parameter type

   The parameter texts is declared as text, not text[]. VARIADIC requires an array type.

2. Step 2: Identify error cause

   PostgreSQL will raise a syntax error because VARIADIC must be followed by an array type, e.g., VARIADIC texts text[].

3. Final Answer:

   Error: VARIADIC parameter must be declared as an array type -> Option A

4. Quick Check:

   VARIADIC needs array type parameter [OK]

Hint: VARIADIC param must be array type, else syntax error [OK]

Common Mistakes:

• Declaring VARIADIC param as scalar type
• Expecting no error with wrong type
• Confusing error with missing FROM clause

5. You want to create a function that accepts a fixed integer and then any number of text arguments variadically. Which of these function declarations is correct?

hard

A. CREATE FUNCTION example(fixed int, VARIADIC texts text) RETURNS text AS $$ ... $$ LANGUAGE sql;

B. CREATE FUNCTION example(VARIADIC texts text[], fixed int) RETURNS text AS $$ ... $$ LANGUAGE sql;

C. CREATE FUNCTION example(fixed int, texts VARIADIC text[]) RETURNS text AS $$ ... $$ LANGUAGE sql;

D. CREATE FUNCTION example(fixed int, VARIADIC texts text[]) RETURNS text AS $$ ... $$ LANGUAGE sql;

Solution

1. Step 1: Recall VARIADIC position rule

   VARIADIC parameter must be the last parameter in the function declaration.

2. Step 2: Check parameter types and order

   CREATE FUNCTION example(fixed int, VARIADIC texts text[]) RETURNS text AS $$ ... $$ LANGUAGE sql; places fixed int first and VARIADIC texts text[] last, which is correct. CREATE FUNCTION example(VARIADIC texts text[], fixed int) RETURNS text AS $$ ... $$ LANGUAGE sql; places VARIADIC first, which is invalid. CREATE FUNCTION example(fixed int, texts VARIADIC text[]) RETURNS text AS $$ ... $$ LANGUAGE sql; misplaces VARIADIC keyword. CREATE FUNCTION example(fixed int, VARIADIC texts text) RETURNS text AS $$ ... $$ LANGUAGE sql; uses non-array type with VARIADIC.

3. Final Answer:

   CREATE FUNCTION example(fixed int, VARIADIC texts text[]) RETURNS text AS $$ ... $$ LANGUAGE sql; -> Option D

4. Quick Check:

   VARIADIC last and array type = correct [OK]

Hint: VARIADIC param must be last and array type [OK]

Common Mistakes:

• Placing VARIADIC parameter before fixed parameters
• Using scalar type with VARIADIC
• Misplacing VARIADIC keyword after parameter name