Overview of Views

Private: Mastering SQL using Postgresql Writing Advanced SQL Queries Overview of Views

Here are the details related to views.

View is nothing but a named query. We typically create views for most commonly used queries.
Unlike tables, views does not physically store the data and when ever we write a query against view it will fetch the data from underlying tables defined as part of the views.
We can perform DML operations over the tables via views with restrictions (for example, we cannot perform DML operations on views with joins, group by etc).
Views that can be used to perform DML operations on underlying tables are called as updatable views
Views can be used to provide restricted permissions on tables for DML Operations. However, it is not used these days.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

CREATE OR REPLACE VIEW orders_v
AS
SELECT * FROM orders

Done.

[]

%%sql

CREATE VIEW orders_v
AS
SELECT * FROM orders

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
(psycopg2.errors.DuplicateTable) relation "orders_v" already exists

[SQL: CREATE VIEW orders_v AS
SELECT * FROM orders]
(Background on this error at: http://sqlalche.me/e/13/f405)

%%sql

SELECT * FROM information_schema.tables
WHERE table_name ~ 'orders'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
2 rows affected.

table_catalog	table_schema	table_name	table_type	self_referencing_column_name	reference_generation	user_defined_type_catalog	user_defined_type_schema	user_defined_type_name	is_insertable_into	is_typed	commit_action
itversity_retail_db	public	orders	BASE TABLE	None	None	None	None	None	YES	NO	None
itversity_retail_db	public	orders_v	VIEW	None	None	None	None	None	YES	NO	None

%%sql

UPDATE orders_v
SET order_status = lower(order_status)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
68883 rows affected.

[]

%%sql

SELECT * FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status
122	2013-07-26 00:00:00	2071	processing
123	2013-07-26 00:00:00	3695	pending_payment
124	2013-07-26 00:00:00	2374	complete
125	2013-07-26 00:00:00	4611	pending_payment
126	2013-07-26 00:00:00	610	complete
127	2013-07-26 00:00:00	5261	pending_payment
128	2013-07-26 00:00:00	2772	pending_payment
129	2013-07-26 00:00:00	9937	closed
130	2013-07-26 00:00:00	7509	pending_payment
131	2013-07-26 00:00:00	10072	processing

%%sql

UPDATE orders_v
SET order_status = upper(order_status)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
68883 rows affected.

[]

%%sql

CREATE OR REPLACE VIEW order_details_v
AS
SELECT * FROM orders o
    JOIN order_items oi
        ON o.order_id = oi.order_item_order_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

SELECT * FROM order_details_v LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status	order_item_id	order_item_order_id	order_item_product_id	order_item_quantity	order_item_subtotal	order_item_product_price
1	2013-07-25 00:00:00	11599	CLOSED	1	1	957	1	299.98	299.98
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	2	2	1073	1	199.99	199.99
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	3	2	502	5	250.0	50.0
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	4	2	403	1	129.99	129.99
4	2013-07-25 00:00:00	8827	CLOSED	5	4	897	2	49.98	24.99
4	2013-07-25 00:00:00	8827	CLOSED	6	4	365	5	299.95	59.99
4	2013-07-25 00:00:00	8827	CLOSED	7	4	502	3	150.0	50.0
4	2013-07-25 00:00:00	8827	CLOSED	8	4	1014	4	199.92	49.98
5	2013-07-25 00:00:00	11318	COMPLETE	9	5	957	1	299.98	299.98
5	2013-07-25 00:00:00	11318	COMPLETE	10	5	365	5	299.95	59.99

%%sql

SELECT count(1) FROM order_details_v

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
172198

%%sql

SELECT order_date,
    order_item_product_id,
    round(sum(order_item_subtotal)::numeric, 2) AS revenue
FROM order_details_v 
GROUP BY order_date,
    order_item_product_id
ORDER BY order_date,
    revenue DESC
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_date	order_item_product_id	revenue
2013-07-25 00:00:00	1004	10799.46
2013-07-25 00:00:00	957	9599.36
2013-07-25 00:00:00	191	8499.15
2013-07-25 00:00:00	365	7558.74
2013-07-25 00:00:00	1073	6999.65
2013-07-25 00:00:00	1014	6397.44
2013-07-25 00:00:00	403	5589.57
2013-07-25 00:00:00	502	5100.00
2013-07-25 00:00:00	627	2879.28
2013-07-25 00:00:00	226	599.99

%%sql

SELECT * FROM order_details_v
WHERE order_id = 2

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.

order_id	order_date	order_customer_id	order_status	order_item_id	order_item_order_id	order_item_product_id	order_item_quantity	order_item_subtotal	order_item_product_price
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	2	2	1073	1	199.99	199.99
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	3	2	502	5	250.0	50.0
2	2013-07-25 00:00:00	256	PENDING_PAYMENT	4	2	403	1	129.99	129.99

Note

We cannot directly update data in tables via views when the view is defined with joins. Even operations such as GROUP BY or ORDER BY etc will make views not updatable by default.

%%sql

UPDATE order_details_v
SET
    order_status = 'pending_payment'
WHERE order_id = 2

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
(psycopg2.errors.ObjectNotInPrerequisiteState) cannot update view "order_details_v"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

[SQL: UPDATE order_details_v SET order_status = 'pending_payment'
WHERE order_id = 2]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Previous Lesson

Back to Lesson

Next Topic