Display Sorted Customer Records with a Parameterized function

Sorted Customer List

Write a PostgreSQL query to create a function that returns a result set of customers with an optional sorting order based on an input parameter (ASC/DESC) for the customer name.

Solution:

-- Create or replace a function named get_sorted_customers that accepts a parameter sort_order of type TEXT
CREATE OR REPLACE FUNCTION get_sorted_customers(sort_order TEXT) 
-- Specify that the function returns a set of rows of type Customers
RETURNS SETOF Customers AS $$
-- Begin the function block
BEGIN
    -- Check if sort_order is equal to 'DESC'
    IF sort_order = 'DESC' THEN
        -- Return the query result selecting all customers ordered by name in descending order
        RETURN QUERY SELECT * FROM Customers ORDER BY name DESC;
    ELSE
        -- Return the query result selecting all customers ordered by name in ascending order
        RETURN QUERY SELECT * FROM Customers ORDER BY name ASC;
    END IF;
    -- End the function block and return the result set
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to return customer records sorted in either ascending or descending order.
• This demonstrates the use of conditional logic within functions to alter query behavior.
• Key Components:
• IF sort_order = 'DESC' THEN ... ELSE ... : Implements conditional sorting.
• ORDER BY name ASC/DESC : Sorts the result set based on the customer name.
• Real-World Application:
• Useful for applications where user preferences dictate data presentation order.

Notes:

• Validate the sort_order parameter to handle unexpected values.
• Ensure the Customers table has a name column.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns a sorted customer list based on their last purchase date, with order direction controlled by an input parameter.
• Write a PostgreSQL function that returns a sorted customer list and includes a computed loyalty points column.
• Write a PostgreSQL function that returns a sorted customer list, excluding customers flagged as inactive.
• Write a PostgreSQL function that returns a sorted customer list and ranks customers based on their total spend.

Go to:

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

PREV : Products under a Specified Price.
NEXT : Employee and Department Join.

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