The hotel has a script that is executed nightly that will send emails to it customers. Suppose that script determines what emails should be sent by looking in the database in a table called
pending_emailsthat has the following fields:
other_id. For now let's assume there are only two types of emails to be sent. If the
email_typeis 1 then the email is for the purpose of notifying the customer that the credit card they have on file has expired. If the
email_typeis 2 then the email is for the purpose of reminding the customer that they have a reservation the next day.
other_idfield will hold a
email_typeis 1 and will hold a
For this assignment you will not write an email-sending script. Instead
you will write a PLPGSQL function that (presumably) would also be run nightly
for the purpose of populating the
pending_emails table as described
above. To do so modify your SQL script to complete these steps:
email_addressfield to the
customertable and add insert statements to populate the field with some bogus email addresses.
pending_emailstable that has the structure described above. Also, create a
sent_emailstable that has the same structure as the
pending_emailstable. We want to keep track of emails we have already sent because we don't want to annoy our customers by sending them daily notifications for which they have already been contacted.
populate_pending_emailsthat accepts a date as a parameter and performs the following actions:
pending_emailstable to the
pending_emailstable (unless, of course, there exists a corresponding entry in the
pending_emailstable. Again, be sure to check the
populate_pending_emailsfunction that you feel demonstrates that it works properly. You code should be written so that I can run the entire script cleanly and see the results of your function calls by inspecting the new tables.