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;

Need a 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;

Need a 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;

Need a 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;

Need a hint?

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