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)