Well done!
In some scenarios, we might need to perform unsubscribe operations for a given customer and all of his/her newsletters. To do so, we can overload the existing opt_in_out() function.
Overload the existing opt_in_out() function. You may take a look at its definition in the tab. (Stretch the panel, if it's too narrow). This time, the function should take only two 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.If p_opt_in is true, the function should subscribe the customer to all newsletters to which they are not currently subscribed (we don't want any duplicates in the subscription table). Log the following message with the 'INFO' level:
Customer with ID of {p_cust_id} added to {p_nr_rows} newsletter(s)
Else, the function should unsubscribe the customer from every newsletter and log the following message, again with the 'INFO' level:
Customer with ID of {p_cust_id} removed from {p_nr_rows} newsletter(s)To catch the number of inserted/deleted rows, use this handy trick:
WITH rows AS ( INSERT INTO subscription ... RETURNING 1 ) SELECT COUNT(*) INTO p_nr_rows FROM rows;
