Views and data abstraction

Throughout our lessons you will have noticed that queries (especially complex JOINs or aggregations like GROUP BY) start to get long and full of rules and calculations.

What if there were a way to mentally save the completed query and hide it from view, pretending from then on that it is, to all intents and purposes, a normal "table" of the DB?

Welcome to the world of Views.

CREATE VIEW

A view is essentially a persisted, named SQL query, but it behaves just like a "virtual" table!

CREATE VIEW user_spending AS
SELECT
  c.id,
  c.first_name,
  SUM(o.amount) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

Done! Now, instead of always having to redo that JOIN and GROUP BY, you just have to write perfectly normal queries:

SELECT * FROM user_spending WHERE total_spent > 100;
-- Postgres dietro le quinte richiamerà automaticamente la grande formula.

Views and "real-time data"

A very common myth is thinking that Views take the data, compute sums like prices or rankings and produce some sad persisted snapshot, like a photograph. Wrong (almost always)! By default, Views are virtual: every time you put the name of a View in your SELECT, Postgres behind the scenes merges your request by running a real-time computation based on the state of the DB at that exact millisecond. So you will never read stale data.

What are they for?

• Simplicity: views protect external apps from changes. If the frontend team knows it has to read data from the top_selling view, they don't have to worry if tomorrow we modify the database by adding three intermediate relations to pull out the top sellers!
• Security and segregation: in huge DBs, a view can pre-select and hide part of the user data from external developers, granting them access (for example) only to a sub-view of analytics scrubbed of real names.

Dropping a View

Just like tables, when no longer needed you proceed with the DROP command:

DROP VIEW user_spending;

Try it

Calling a View