Retrieve Employees by Department using a Function

Filter Employees by Department

Write a PostgreSQL query to create a function that returns a result set of employees filtered by a given department ID.

Solution:

-- Create or replace a function named get_employees_by_dept that accepts an integer parameter dept_id
CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_id INT) 
-- Specify that the function returns a set of rows of type Employees
RETURNS SETOF Employees AS $$
-- Begin the function block
BEGIN
    -- Return the result of the following query
    RETURN QUERY 
    -- Select all columns from the Employees table
    SELECT * FROM Employees 
    -- Filter rows where department_id matches the provided dept_id parameter
    WHERE department_id = dept_id;
-- End the function block
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to return only the employees belonging to a specified department.
• This demonstrates how to use function parameters to filter result sets.
• Key Components:
• Parameter dept_id INT : Accepts the department ID for filtering.
• WHERE department_id = dept_id : Filters rows based on the provided department ID.
• Real-World Application:
• Ideal for departmental reporting systems or access control based on department.

Notes:

• Ensure the Employees table contains a department_id column.
• Validate input parameters as needed in production environments.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that filters employees by department name instead of department ID.
• Write a PostgreSQL function that returns employees from multiple departments using an array of department IDs.
• Write a PostgreSQL function that filters employees by department and returns only those with over five years of service.
• Write a PostgreSQL function that filters employees by department and sorts the result by salary in descending order.

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

Previous PostgreSQL Exercise: Return All Employees.