HW11: PlPgSQL due Mon 10 Apr 13:00

Description

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_emails that has the following fields: customer_id, email_type, other_id. For now let's assume there are only two types of emails to be sent. If the email_type is 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_type is 2 then the email is for the purpose of reminding the customer that they have a reservation the next day.

The other_id field will hold a payment_method_id value if email_type is 1 and will hold a reservation_id value if email_type is 2.

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:

• (2 pts) Add an email_address field to the customer table and add insert statements to populate the field with some bogus email addresses.
• (4 pts) Create a pending_emails table that has the structure described above. Also, create a sent_emails table that has the same structure as the pending_emails table. 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.
• (18 pts) Write PLPGSQL function called populate_pending_emails that accepts a date as a parameter and performs the following actions:
  1. Move all entries from the pending_emails table to the sent_emails table.
  2. For all forms of payment on file find any that have expired (i.e, whose expiration date comes before the date passed to the function as a parameter) create an entry in the pending_emails table (unless, of course, there exists a corresponding entry in the sent_emails table).
  3. For all reservations that are only one day away (in the future) from the date passed as a parameter add an appropriate entry in the pending_emails table. Again, be sure to check the sent_emails table first.
• (4 pts) Write a series of calls to your populate_pending_emails function 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.

Grading and Submission