Can I create an email for the user based on input for the first and last name using postgreSQL?

Question

I am pretty new to Postgres, and I am making a mock database that manages students for a university so I can get a better understanding of how Postgres works.

I am creating a student table that has columns for student id, enrollment date, grad year, first/last name, and a university email address. At the moment I just have the email column set to NOT NULL and the user has to enter their own email address, but clearly this is not how email addresses at a university work.

I was wondering if there was a way to make the email be created dynamically based off of what the user inputs as their first and last name. For example, if I were to run the following query:

INSERT INTO students (first_name, last_name) VALUES ('bob', 'smith');

An email address would be created on the fly for bob smith in the format 'bobsmith@university.com', so my table would look like:

 student_id | enrollment_date | grad_year |             email              | student_first | student_last 
------------+-----------------+-----------+--------------------------------+---------------+--------------
          2 | 2020-12-28      |    2024   |     bobsmith@university.com    |      bob      | smith

Is there a way that I can create a function that takes the values from the student_first and student_last columns and create an email based on them?

Any links to readable documentation would be useful.

Answer 1

There are couple ways to accomplish what you want: a function/procedure or a (I hate to say) a Trugger. However, generated column will not work in this case: What happens when a second student "Bob Smith" registers. You can use a function to generate the email address - with a number, if needed, to make it unique.

create or replace 
function generate_email_address(first_name_in text
                               ,last_name_in  text
                               )
  returns text 
  language sql 
as $$
   with email(num) as 
        ( select count(*) 
            from students 
           where lower(first_name) = lower(first_name_in) 
             and lower(last_name)  = lower(last_name_in) 
        ) 
   select lower(first_name_in) ||
          lower(last_name_in)  || 
          case when num = 0 then '' else to_char(num+1,'FM99') end ||
          '@university.com'
     from email; 
$$; 

See example: