List Products under a set price Using a PostgreSQL Function

Products under a Specified Price

Write a PostgreSQL query to create a function that returns a result set of products with a price lower than a given value.

Solution:

-- Create or replace a function named get_affordable_products that accepts a parameter max_price of type NUMERIC
CREATE OR REPLACE FUNCTION get_affordable_products(max_price NUMERIC) 
-- Specify that the function returns a set of rows of type Products
RETURNS SETOF Products AS $$
-- Begin the function block
BEGIN
    -- Execute the query and return its result set
    RETURN QUERY 
    -- Select all columns from the Products table
    SELECT * FROM Products 
    -- Filter the products to include only those with a price less than max_price
    WHERE price < max_price;
    -- End the function block and return the result set
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to filter products based on a maximum price criteria.
• This demonstrates filtering in set-returning functions using input parameters.
• Key Components:
• Parameter max_price NUMERIC : Defines the upper limit for product prices.
• WHERE price < max_price : Applies the price filter on the Products table.
• Real-World Application:
• Ideal for e-commerce platforms and price-sensitive product displays.

Notes:

• Ensure the Products table includes a price column.
• Consider adding error handling for invalid price inputs.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns products under a specified price and orders them by price ascending.
• Write a PostgreSQL function that returns products under a specified price and filters out products with zero available stock.
• Write a PostgreSQL function that returns products under a specified price and adds a column for the computed discounted price.
• Write a PostgreSQL function that returns products under a specified price and joins the results with supplier details.

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

Previous PostgreSQL Exercise: Customer Details with RETURNS TABLE.