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)