Book Image

PostgreSQL Development Essentials

By : Baji Shaik
Book Image

PostgreSQL Development Essentials

By: Baji Shaik

Overview of this book

PostgreSQL is the most advanced open source database in the world. It is easy to install, configure, and maintain by following the documentation; however, it’s difficult to develop applications using programming languages and design databases accordingly. This book is what you need to get the most out of PostgreSQL You will begin with advanced SQL topics such as views, materialized views, and cursors, and learn about performing data type conversions. You will then perform trigger operations and use trigger functions in PostgreSQL. Next we walk through data modeling, normalization concepts, and the effect of transactions and locking on the database. The next half of the book covers the types of indexes, constrains, and the concepts of table partitioning, as well as the different mechanisms and approaches available to write efficient queries or code. Later, we explore PostgreSQL Extensions and Large Object Support in PostgreSQL. Finally, you will perform database operations in PostgreSQL using PHP and Java. By the end of this book, you will have mastered all the aspects of PostgreSQL development. You will be able to build efficient enterprise-grade applications with PostgreSQL by making use of these concepts
Table of Contents (17 chapters)
PostgreSQL Development Essentials
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface

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