Creating views

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:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE [condition];

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 the FROM clause, you can provide the joining condition under the WHERE 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:

CREATE TABLE suppliers
(supplier_id number primary key,
Supplier_name varchar(30),
Phone_number number);
CREATE TABLE orders
(order_number number primary key,
Supplier_id number references suppliers(supplier_id),
Quanity number,
Is_active varchar(10),
Price number);
CREATE VIEW active_supplier_orders AS 
SELECT suppliers.supplier_id, suppliers.supplier_name orders.quantity, orders.price 
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'XYZ COMPANY'
And orders.active='TRUE';

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:

SELECT * FROM active_supplier_orders;

Deleting and replacing views

To delete a view, simply use the DROP VIEW statement with view_name. The basic DROP VIEW syntax is as follows:

DROP VIEW IF EXISTS view_name;

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:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name(s)
WHERE condition;

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 the FROM clause, you can provide the joining condition under the WHERE 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.

CREATE OR REPLACE VIEW active_supplier_orders AS 
SELECT suppliers.supplier_id, suppliers.supplier_name orders.quantity, orders.price,order. order_number 
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'XYZ COMPANY'
And orders.active='TRUE';;