Combine Employee and Department Data Using a PostgreSQL function

Employee and Department Join

Write a PostgreSQL query to create a function that returns a result set by joining the Employees and Departments tables on the department_id.

Solution:

-- Create or replace a function named get_employee_department_info
CREATE OR REPLACE FUNCTION get_employee_department_info() 
-- Specify that the function returns a table with columns: employee_id (INT), employee_name (TEXT), and department_name (TEXT)
RETURNS TABLE(employee_id INT, employee_name TEXT, department_name TEXT) AS $$
-- Begin the function block
BEGIN
    -- Execute and return the result of the following query
    RETURN QUERY 
    -- Select employee id, employee name, and department name from the joined tables
    SELECT e.id, e.name, d.name 
    -- From the Employees table aliased as e
    FROM Employees e
    -- Join the Departments table aliased as d where employee's department_id matches department's id
    JOIN Departments d ON e.department_id = d.id;
-- End the function block
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to retrieve combined data from Employees and Departments.
• This demonstrates joining tables within a function that returns a composite result set.
• Key Components:
• JOIN Departments d ON e.department_id = d.id : Performs the join on the department_id.
• RETURNS TABLE(...) : Defines the structure of the returned result.
• Real-World Application:
• Useful for dashboards and reports that need comprehensive employee and department information.

Notes:

• Both Employees and Departments tables must be present and correctly related.
• Adjust column names as necessary to match table definitions.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns employee and department details and includes the department location.
• Write a PostgreSQL function that returns employee and department details, filtering for employees with salaries above a given threshold.
• Write a PostgreSQL function that returns employee and department details and calculates each employee’s tenure in the company.
• Write a PostgreSQL function that returns employee and department details and adds a computed column for the department’s average salary.

Go to:

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

PREV : Sorted Customer List.
NEXT : Return Orders Cursor.

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