Retrieve Sales Data Within a Specific Date Range with a function

Filtered Sales by Date Range

Write a PostgreSQL query to create a function that returns a result set of sales records within a specified date range.

Solution:

-- Create or replace a function named get_sales_by_date with two DATE parameters: start_date and end_date
CREATE OR REPLACE FUNCTION get_sales_by_date(start_date DATE, end_date DATE) 
-- Specify that the function returns a set of rows of type Sales
RETURNS SETOF Sales AS $$
-- Begin the function block
BEGIN
    -- Execute the following query and return its result set
    RETURN QUERY 
    -- Select all columns from the Sales table
    SELECT * FROM Sales 
    -- Filter the sales records where sale_date is between the provided start_date and end_date
    WHERE sale_date BETWEEN start_date AND end_date;
    -- End the function block and return the result set
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to filter sales records based on a date range provided by the user.
• This demonstrates the use of function parameters to tailor the returned result set.
• Key Components:
• Parameters start_date and end_date : Define the date range for filtering.
• WHERE sale_date BETWEEN start_date AND end_date : Applies the date filter on the Sales table.
• Real-World Application:
• Useful for generating period-specific sales reports and analytics.

Notes:

• Verify that the Sales table includes a sale_date column.
• Consider adding input validation for the date parameters.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns sales records within a specified date range and filters by product category.
• Write a PostgreSQL function that returns sales records within a specified date range and groups the results by salesperson.
• Write a PostgreSQL function that returns sales records within a specified date range and adds a computed tax amount column.
• Write a PostgreSQL function that returns sales records within a specified date range and sorts them by sale amount in descending order.

Go to:

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

PREV : Return Orders Cursor.

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