Retrieve Employee Full Name using a PostgreSQL Function

Function to Get Employee Full Name

Write a PostgreSQL function that concatenates an employee’s first name and last name from the Employees table.

Solution:

-- Create a function named get_full_name that takes an employee ID as input and returns a text value
CREATE FUNCTION get_full_name(emp_id INT) RETURNS TEXT AS $$
-- Declare a variable to store the full name
DECLARE 
    full_name TEXT;
BEGIN
    -- Concatenate the first name and last name of the employee with the given ID and store it in full_name
    SELECT first_name || ' ' || last_name INTO full_name 
    -- Select from the Employees table
    FROM Employees 
    -- Filter by the provided employee ID
    WHERE employee_id = emp_id;
    -- Return the full name
    RETURN full_name;
END;
-- Specify the language used in the function as PL/pgSQL
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• Retrieves and returns the full name of an employee based on their ID.
• Key Components:
• SELECT first_name || ' ' || last_name INTO full_name → Concatenates first and last name.
• WHERE employee_id = emp_id → Filters by the given employee ID.
• Real-World Application:
• Used in HR applications to fetch employee details efficiently.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns the cube of a given number.
• Write a PostgreSQL function that calculates the absolute difference between two integers.
• Write a PostgreSQL function that squares a number only if it is even, otherwise returns -1.
• Write a PostgreSQL function that returns the square root of a number rounded to two decimal places.

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

Previous PostgreSQL Exercise: Function to Calculate the Square of a Number.
Next PostgreSQL Exercise: Function to Check if a Number is Even or Odd.