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
1. The log table
Loading Customers
Subscriptions
Congratulations

Instruction

Hello, and welcome to the last part of our course!

In this part, you'll complete practical tasks and exercises to apply what you learned in this course: variables, functions, exception handling, etc.

During this quiz, you'll create new functions that implement ETL processes for a newsletter subscription system. ETL is short for Extract, Transform, and Load processes used to collect data from various sources, transform the data, and load it into a destination data store. In practice, the destination data store is a data warehouse – a large store of data accumulated from a wide range of sources within a company and used to guide management decisions.

In this part, we'll be working with a sample database that contains customers, newsletters, subscriptions, and log tables.

Let's review the core database table. The first table we're going to work with is log. This table stores our custom log messages for various events, such as:

  • The start of a process.
  • The end of a process.
  • A description of an error that was thrown.
  • And the number of record insertions, deletions, and updates that took place while the database was running.

Exercise

The log table has the following columns:

  • id – a unique identifier.
  • process_name – the function name (e.g., 'load_customers()') a given log comes from.
  • message_text – the message that was logged.
  • level – denotes whether it's a regular log or an error.
  • logged_on – timestamp of when the activity was logged.

Select all data from the log table. The table is empty and ready for the next exercises.