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
2. Create a logging function
Loading Customers
Subscriptions
Congratulations

Instruction

Perfect! The log table is empty, so we can get cracking on the next exercises.

Before we start implementing new business functions, we should have some custom logging mechanism in place so we know the following, among other things:

  • How many records were updated/inserted.
  • Which ETL process, if any, failed.
  • What the error message was.
  • Whether the ETL process finished successfully.

Exercise

Create a new function named log_message() that returns void and inserts a new message into the log table. The structure of the existing log table looks like this: 

CREATE TABLE log (
  id serial NOT NULL PRIMARY KEY,
  process_name character varying(256),
  message_text character varying(2000),
  level character varying(32),
  logged_on timestamp(0)
);

The new function must accept the following three parameters:

  • p_process_name character varying(256),
  • p_message_text character varying(2000),
  • p_level character varying(32).

Every single time we call this function with specific parameters, it should insert a new message into the log table setting logged_on to current_timestamp.

One more requirement: the level column should always be inserted into the log table in uppercase format, so you'll have to implement this logic in the log_message() function as well. Use the UPPER() function to uppercase the level.