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 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:
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 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.
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';;