Extract Customer information with a Table-Returning Function

Customer Details with RETURNS TABLE

Write a PostgreSQL query to create a function using the RETURNS TABLE clause to return customer details (ID, name, email) from the Customers table.

Solution:

-- Create or replace a function named get_customer_details
CREATE OR REPLACE FUNCTION get_customer_details() 
-- Specify that the function returns a table with columns: customer_id (INT), customer_name (TEXT), and customer_email (TEXT)
RETURNS TABLE(customer_id INT, customer_name TEXT, customer_email TEXT) AS $$
-- Begin the function block
BEGIN
    -- Return the result of the following query
    RETURN QUERY 
    -- Select the id, name, and email columns from the Customers table
    SELECT id, name, email FROM Customers;
-- End the function block and return the result set
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to return specific columns (ID, name, email) for all customers.
• This demonstrates how to use the RETURNS TABLE clause for defining output structure.
• Key Components:
• RETURNS TABLE(customer_id INT, customer_name TEXT, customer_email TEXT) : Specifies the columns and data types.
• SELECT id, name, email FROM Customers : Retrieves the desired customer details.
• Real-World Application:
• Useful for applications that require standardized customer information for display or processing.

Notes:

• Ensure the Customers table contains id, name, and email columns.
• The structure can be extended to include additional customer attributes.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function using RETURNS TABLE to return customer details along with their total number of orders.
• Write a PostgreSQL function using RETURNS TABLE to return customer details and flag VIP customers based on purchase history.
• Write a PostgreSQL function using RETURNS TABLE to return customer details along with their most recent order date.
• Write a PostgreSQL function using RETURNS TABLE to return customer details and calculate each customer’s total spending.

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

Previous PostgreSQL Exercise: Return Orders Above a Threshold.