OUT parameters in PostgreSQL - Mini Project: Build & Apply

Using OUT Parameters in PostgreSQL Functions

📖 Scenario: You are managing a small bookstore database. You want to create a function that calculates the total price of books bought and also returns the number of books purchased.

🎯 Goal: Create a PostgreSQL function with OUT parameters that returns both the total price and the count of books bought.

📋 What You'll Learn

Create a function named calculate_total with two OUT parameters: total_price (numeric) and book_count (integer).

The function should accept an array of book prices as input.

Inside the function, calculate the sum of all book prices and the count of books.

Assign these values to the OUT parameters.

The function should return the OUT parameters automatically.

💡 Why This Matters

🌍 Real World

Functions with OUT parameters are useful when you want to return multiple values from a database operation, such as totals and counts in sales or inventory systems.

💼 Career

Database developers and administrators often write stored functions with OUT parameters to encapsulate logic and return multiple results cleanly.

1

Create the function header with OUT parameters

Write the first line to create a function named calculate_total that accepts one parameter prices of type numeric[] and has two OUT parameters: total_price numeric and book_count integer.

PostgreSQL

CREATE FUNCTION calculate_total(prices numeric[], OUT total_price numeric, OUT book_count integer)
-- Your code here

Need a hint?

Use CREATE FUNCTION with the function name and parameters. Declare OUT parameters inside the parentheses.

2

Add the function body with BEGIN and END

Add the BEGIN and END keywords to start and end the function body after the function header.

PostgreSQL

CREATE FUNCTION calculate_total(prices numeric[], OUT total_price numeric, OUT book_count integer)
BEGIN
-- Your code here
END;

Need a hint?

Use BEGIN and END; to define the function body.

3

Calculate total price and book count inside the function

Inside the function body, assign the sum of all elements in prices to total_price and the count of elements in prices to book_count. Use SELECT with SUM and ARRAY_LENGTH.

PostgreSQL

CREATE FUNCTION calculate_total(prices numeric[], OUT total_price numeric, OUT book_count integer)
BEGIN
    -- Your code here
END;

Need a hint?

Use UNNEST(prices) AS p to turn the array into rows, then sum them. Use ARRAY_LENGTH(prices, 1) to get the count.

4

Complete the function with language and semicolon

Add the LANGUAGE plpgsql; declaration at the end of the function to specify the language and end the statement with a semicolon.

PostgreSQL

CREATE FUNCTION calculate_total(prices numeric[], OUT total_price numeric, OUT book_count integer)
BEGIN
    SELECT SUM(p), ARRAY_LENGTH(prices, 1) INTO total_price, book_count FROM UNNEST(prices) AS p;
END;
-- Your code here

Need a hint?

Always specify the language of the function with LANGUAGE plpgsql; at the end.