Retrieve High-Value Orders with a PostgreSQL Function

Return Orders Above a Threshold

Write a PostgreSQL query to create a function that returns a result set of orders where the total exceeds a specified threshold.

Solution:

-- Define or replace a function named get_large_orders that accepts a parameter min_total of type NUMERIC
CREATE OR REPLACE FUNCTION get_large_orders(min_total NUMERIC) 
-- Specify that the function returns a set of rows from the Orders table
RETURNS SETOF Orders AS $$
-- Begin the function block
BEGIN
    -- Execute a query to return orders where the total is greater than the provided min_total
    RETURN QUERY 
    -- Select all columns from the Orders table
    SELECT * FROM Orders 
    -- Filter the orders to include only those with a total greater than min_total
    WHERE total > min_total;
    -- End the function block and return the result set
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to retrieve orders with totals greater than a user-specified minimum.
• This demonstrates parameterized queries within functions.
• Key Components:
• Parameter min_total NUMERIC : Sets the minimum order total for filtering.
• WHERE total > min_total : Filters orders based on the provided threshold.
• Real-World Application:
• Beneficial for financial analysis or triggering alerts on high-value orders.

Notes:

• Verify that the Orders table includes a total column.
• Parameter validation may be added for robust error handling.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns orders above a specified threshold and includes the order date in the result.
• Write a PostgreSQL function that returns orders above a given threshold and sorts them by total in descending order.
• Write a PostgreSQL function that returns orders above a threshold and joins them with corresponding customer details.
• Write a PostgreSQL function that returns orders above a threshold and calculates a discount percentage based on the order total.

Go to:

• Practical Techniques for Returning Result Sets in PostgreSQL Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Aggregated Sales Data by Region.
NEXT : Customer Details with RETURNS TABLE.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.