PostgreSQL Advanced Building Blocks

This chapter will introduce the remainder of the PostgreSQL building blocks, including views, indexes, functions, triggers, and rules. In addition to that, the web car portal schema will be revised. Several Data Definition Language (DDL) commands, such as CREATE and ALTER, will also be introduced. Since the lexical structure and several Data Manipulation Language (DML) commands haven't been introduced yet, we will try to use simple DML commands.

In this chapter, the following topics will be covered:

• Views: Views are an important part of database modeling because they act as an interface or as an abstraction layer. The Views section will cover view synopsis and usages, and an updatable view example will be demonstrated. 
• Indexes: Indexes are the secret sauce for ensuring consistency and performance. Index types will be discussed.
• Functions: Functions can be used to perform very complex logic in the database. Also, they can be used to return scalar values or datasets. Functions will be discussed briefly here, since functions are discussed in detail in Chapter 07, Server-Side Programming with PL/pgSQL.
• User-defined data types: One big advantage of PostgreSQL is being able to define and use new, different data types; this section will show several use cases, wherein user-defined data types will be used to solve some issues. 
• Triggers and rule systems: Triggers and rule systems allow developers to handle events triggered by INSERT, UPDATE, DELETE, and so on. The trigger system is used to model complex business requirements that are difficult to achieve using plain SQL.