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
10. Create a function
Congratulations

Instruction

Good stuff! We're familiar with the structure of the newsletter and subscription tables, so we can go ahead and implement a function to unsubscribe a customer from a given newsletter.

Exercise

Create a new function named opt_in_out() that takes three parameters:

  • p_opt_in boolean – a flag denoting whether a customer wants to opt in (TRUE) or opt out (FALSE).
  • p_cust_id int – the customer's ID.
  • p_newsl_id int – the newsletter's ID.

The function should perform the following operations:

  1. Create a p_exists boolean variable that will store TRUE if there exists a row in the subscription table that matches the function's arguments.
  2. Create separate cases:
    1. If p_opt_in AND NOT p_exists, then insert these values into the subscription table: p_cust_id, p_newsl_id, and current_timestamp.
    2. If NOT p_opt_in AND p_exists, then delete the proper row from the subscription table.

    3. If p_opt_in AND p_exists, then log the following information:

      Argument Value
      p_process_name 'opt_in_out()'
      p_message_text 'Customer with ID of {p_cust_id} has already subscribed to the newsletter with ID of {p_newsl_id}'
      p_level 'info'

      Finish the execution here.

    4. In other cases, log the following information:

      Argument Value
      p_process_name 'opt_in_out()'
      p_message_text 'Invalid customer with ID of {p_cust_id} or newsletter with ID of {p_newsl_id}'
      p_level 'error'

      Finish the execution here.

  3. Finally, log the following information:

    Argument Value
    p_process_name 'opt_in_out()'
    p_message_text 'Opt in/out request for the customer with ID of {p_cust_id} and newsletter with ID of {p_newsl_id} has been processed'
    p_level 'info'