Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Writing User-Defined Functions in PostgreSQL

Custom logging
Loading Customers
Subscriptions
13. Mailing list function
Congratulations

Instruction

Brilliant! Now we have the opt in/out process implemented.

There is a request from our mailing team to get the list of customers who subscribed to the e-mail newsletters.

Exercise

Create a new function named extract_mailing_list() that returns a table with the following columns:

RETURNS TABLE (
  title character varying(8),
  first_name character varying(255),
  last_name character varying(255),
  email_address character varying(255),
  newsletter_name character varying(256)
)

When it starts, the function should log the following message with the 'INFO' level:

ETL process Extract Mailing List Started

The type of newsletter must equal 'E-mail', and the final result set should contain only distinct records ordered by the email_address column.

When the function finishes, it should log the following message with the 'INFO' level:

ETL process Extract Mailing List Finished

Remember to pass in 'extract_mailing_list()' as the process name when logging.