VARIADIC parameters in PostgreSQL - Mini Project: Build & Apply

Using VARIADIC Parameters in PostgreSQL Functions

📖 Scenario: You are building a PostgreSQL function to help a store manager calculate the total sales from a variable number of daily sales amounts. Instead of passing a fixed number of sales values, the manager wants to pass any number of sales amounts to the function.

🎯 Goal: Create a PostgreSQL function that uses VARIADIC parameters to accept a variable number of sales amounts and returns their total sum.

📋 What You'll Learn

Create a function named total_sales that accepts a VARIADIC parameter of type numeric[].

Inside the function, calculate the sum of all sales amounts passed.

Return the total sum as a numeric value.

Test the function by calling it with different numbers of sales amounts.

💡 Why This Matters

🌍 Real World

Store managers and analysts often need to calculate totals or aggregates from a flexible number of daily sales figures without knowing how many values will be provided in advance.

💼 Career

Understanding VARIADIC parameters in PostgreSQL functions is useful for database developers and analysts who write flexible, reusable database functions that handle variable input sizes.

1

Create the function header with VARIADIC parameter

Write a PostgreSQL function named total_sales that accepts a VARIADIC parameter called sales of type numeric[]. Start the function definition with CREATE OR REPLACE FUNCTION total_sales(VARIADIC sales numeric[]) and specify it returns numeric. Open the function body with AS $$ BEGIN.

PostgreSQL

CREATE OR REPLACE FUNCTION total_sales(VARIADIC sales numeric[])
RETURNS numeric AS $$
BEGIN
-- Your code here
END;
$$ LANGUAGE plpgsql;

Hint

Use VARIADIC sales numeric[] in the function parameters to accept multiple numeric values as an array.

2

Declare a variable to hold the total sum

Inside the function body, declare a variable named total of type numeric and initialize it to 0. This variable will hold the sum of all sales amounts.

PostgreSQL

CREATE OR REPLACE FUNCTION total_sales(VARIADIC sales numeric[])
RETURNS numeric AS $$
DECLARE
    -- Your code here
BEGIN

END;
$$ LANGUAGE plpgsql;

Hint

Use the DECLARE section to create variables inside a PL/pgSQL function.

3

Sum all sales amounts using a loop

Add a FOR loop to iterate over each sale in the sales array. Inside the loop, add each sale value to the total variable.

PostgreSQL

CREATE OR REPLACE FUNCTION total_sales(VARIADIC sales numeric[])
RETURNS numeric AS $$
DECLARE
    total numeric := 0;
BEGIN
    -- Your code here
END;
$$ LANGUAGE plpgsql;

Hint

Use FOR sale IN SELECT unnest(sales) LOOP to loop through each element in the sales array.

4

Return the total sum from the function

Add a RETURN total; statement before the END; keyword to return the calculated sum from the function.

PostgreSQL

CREATE OR REPLACE FUNCTION total_sales(VARIADIC sales numeric[])
RETURNS numeric AS $$
DECLARE
    total numeric := 0;
    sale numeric;
BEGIN
    FOR sale IN SELECT unnest(sales) LOOP
        total := total + sale;
    END LOOP;
    -- Your code here
END;
$$ LANGUAGE plpgsql;

Hint

Use RETURN total; to send the result back from the function.

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