Display all Employee Records with a PostgreSQL Function

Return All Employees

Write a PostgreSQL query to create a function that returns all rows from the Employees table as a result set.

Solution:

-- Define or replace the function get_all_employees
CREATE OR REPLACE FUNCTION get_all_employees() 
-- Specify that the function returns a set of rows of type Employees
RETURNS SETOF Employees AS $$
-- Begin the function block
BEGIN
    -- Execute a query to select all rows from the Employees table
    RETURN QUERY SELECT * FROM Employees;
-- End the function block
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to return the complete set of rows from the Employees table.
• This demonstrates how to use a set-returning function with the RETURN QUERY statement.
• Key Components:
• RETURNS SETOF Employees : Specifies that the function returns a set of rows from the Employees table.
• RETURN QUERY SELECT * FROM Employees : Executes the query and returns its result.
• Real-World Application:
• Useful for applications that require dynamic retrieval of all employee records for reporting or processing.

Notes:

• Ensure the Employees table exists and has the correct structure.
• This function can be extended with parameters for more complex queries.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns all employees with a salary above the company’s average.
• Write a PostgreSQL function that returns all employees sorted by their hire date in descending order.
• Write a PostgreSQL function that returns all employees whose names start with a specified letter.
• Write a PostgreSQL function that returns all employees along with a computed column for years of service.

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

Previous PostgreSQL Exercise: Returning result sets from functions Home.