HW11: PlPgSQL due Mon 10 Apr 13:00

In this assignment you will:
\item Discover ...
difficult (impossible?) to write in SQL alone.


Start this assignment by copying and editing the hotel management script you created for Homework #6. Imagine the following scenario:
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:

Grading and Submission

To turn in the assignment upload into Canvas your .sql script that creates the schema, tables, and PLPGSQL function. Your assignment will be graded out of 28 points as described above.