This book is all about an open source software product, a relational database called PostgreSQL. PostgreSQL is an advanced SQL database server, available on a wide range of platforms. The purpose of this book is to teach database developers the fundamental practices and techniques to program database applications with PostgreSQL.
In this chapter, we will discuss the following advanced SQL topics:
Creating views
Understanding materialized views
Creating cursors
Using the
GROUP BY
clauseUsing the
HAVING
clauseUnderstanding complex topics such as subqueries and joins
A view is a virtual table based on the result set of an SQL statement. Just like a real table, a view consist of rows and columns. The fields in a view are from one or more real tables in the database. Generally speaking, a table has a set of definitions that physically stores data. A view also has a set of definitions built on top of table(s) or other view(s) that does not physically store data. The purpose of creating views is to make sure that the user does not have access to all the data and is being restricted through a view. Also, it's better to create a view if we have a query based on multiple tables so that we can use it straightaway rather than writing a whole PSQL again and again.
Database views are created using the CREATE VIEW
statement. Views can be created from a single table or multiple tables, or another view.
The basic CREATE
VIEW
syntax is as follows:
Let's take a look at each of these commands:
CREATE VIEW
: This command helps create the database's view.SELECT
: This command helps you select the physical and virtual columns that you want as part of the view.FROM
: This command gives the table names with an alias from where we can fetch the columns. This may include one or more table names, considering you have to create a view at the top of multiple tables.WHERE
: This command provides a condition that will restrict the data for a view. Also, if you include multiple tables in theFROM
clause, you can provide the joining condition under theWHERE
clause.
You can then query this view as though it were a table. (In PostgreSQL, at the time of writing, views are read-only by default.) You can SELECT
data from a view just as you would from a table and join it to other tables; you can also use WHERE
clauses. Each time you execute a SELECT
query using the view, the data is rebuilt, so it is always up-to-date. It is not a frozen copy stored at the time the view was created.
Let's create a view on supplier and order tables. But, before that, let's see what the structure of the suppliers
and orders
table is:
The preceding example will create a virtual table based on the result set of the SELECT
statement. You can now query the PostgreSQL VIEW
as follows:
To delete a view, simply use the DROP VIEW
statement with view_name
. The basic DROP
VIEW
syntax is as follows:
If you want to replace an existing view with one that has the same name and returns the same set of columns, you can use a CREATE OR REPLACE
command.
The following is the syntax to modify an existing view:
Let's take a look at each of these commands:
CREATE OR REPLACE VIEW
: This command helps modify the existing view.SELECT
: This command selects the columns that you want as part of the view.FROM
: This command gives the table name from where we can fetch the columns. This may include one or more table names, since you have to create a view at the top of multiple tables.WHERE
: This command provides the condition to restrict the data for a view. Also, if you include multiple tables in theFROM
clause, you can provide the joining condition under theWHERE
clause.
Let's modify a view, supplier_orders
, by adding some more columns in the view. The view was originally based on supplier and order tables having supplier_id
, supplier_name
, quantity
, and price
. Let's also add order_number
in the view.
A materialized view is a table that actually contains rows but behaves like a view. This has been added in the PostgreSQL 9.3 version. A materialized view cannot subsequently be directly updated, and the query used to create the materialized view is stored in exactly the same way as the view's query is stored. As it holds the actual data, it occupies space as per the filters that we applied while creating the materialized view.
Before we get too deep into how to implement materialized views, let's first examine why we may want to use materialized views.
You may notice that certain queries are very slow. You may have exhausted all the techniques in the standard bag of techniques to speed up those queries. In the end, you will realize that getting queries to run as fast as you want simply isn't possible without completely restructuring the data.
Now, if you have an environment where you run the same type of SELECT
query multiple times against the same set of tables, then you can create a materialized view for SELECT
so that, on every run, this view does not go to the actual tables to fetch the data, which will obviously reduce the load on them as you might be running a Data Manipulation Language (DML) against your actual tables at the same time. So, basically, you take a view and turn it into a real table that holds real data rather than a gateway to a SELECT
query.
A materialized view can be read-only, updatable, or writeable. Users cannot perform DML statements on read-only materialized views, but they can perform them on updatable and writeable materialized views.
You can make a materialized view read-only during creation by omitting the FOR UPDATE
clause or by disabling the equivalent option in the database management tool. Read-only materialized views use many mechanisms similar to updatable materialized views, except they do not need to belong to a materialized view group.
In a replication environment, a materialized table holds the table data and resides in a different database. A table that has a materialized view on it is called a master table. The master table resides on a master site and the materialized view resides on a materialized-view site.
In addition, using read-only materialized views eliminates the possibility of introducing data conflicts on the master site or the master materialized view site, although this convenience means that updates cannot be made on the remote materialized view site.
The syntax to create a materialized view is as follows:
The CREATE MATERIALIZED VIEW
command helps us create a materialized view. The command acts in way similar to the CREATE VIEW
command, which was explained in the previous section.
Let's make a read-only materialized view for a supplier table:
This view is a read-only materialized view and will not reflect the changes to the master site.
You can make a materialized view updatable during creation by including the FOR UPDATE
clause or enabling the equivalent option in the database management tool. In order for changes that have been made to an updatable materialized view to be reflected in the master site during refresh, the updatable materialized view must belong to a materialized view group.
When we say "refreshing the materialized view," we mean synchronizing the data in the materialized view with data in its master table.
An updatable materialized view enables you to decrease the load on master sites because users can make changes to data on the materialized view site.
The syntax to create an updatable materialized view is as follows:
Let's make an updatable materialized view for a supplier table:
Whenever changes are made in the suppliers_matview
clause, it will reflect the changes to the master sites during refresh.
A writeable materialized view is one that is created using the FOR UPDATE
clause like an updatable materialized view is, but it is not a part of a materialized view group. Users can perform DML operations on a writeable materialized view; however, if you refresh the materialized view, then these changes are not pushed back to the master site and are lost in the materialized view itself. Writeable materialized views are typically allowed wherever fast-refreshable, read-only materialized views are allowed.