Jose: Hi guys and welcome back.
In this video we're gonna talk about Views.
So this is gonna be a bit of a longer one,
but please bear with me.
Views are pretty important.
They can really help make your life much easier.
But what are Views?
For normal Views, a name for a query.
They behave like a table in many ways
so they're not just a function
and every time the View is accessed,
the query that is associated with that View is executed.
And if this happens,
if every time the View is accessed
that means the query is executed,
then we say the View is not materialised,
because the data that the View represents
is never essentially stored in disc.
Every time you wanna access the View
you run the query.
Let's talk about what Views really are.
So here we've got a relatively complicated query
from the "Distinct On" lecture where we could have
the top voted option for each poll.
And let's say that you're accessing this quite often,
so often in fact that you don't want
to be typing this query out all the time.
Well, what you might do is you might create a View out of it
and that essentially saves it under a name.
So what you would do is you would go ahead
and do "create View",
you would give it a name,
"most voted options" in this case
and then you would say "as"
and then you would put in your query.
Then you can do that.
Select "star" from most voted options
and then when you do that,
Postgre's behind the scenes would run your initial query
and it would give you, in this case, all the columns.
Couple of benefits, you could potentially
not select "star" for most voted options
so you can potentially select fewer columns if you want
and you can do other things in this query,
like more filtering, et cetera,
if that's what you want to do.
So a View is essentially a table
but the data is never actually stored on disc,
the data is always fetched
from running the underlying query.
However, it also comes with a couple extra features.
You've got updatable Views.
These are often called "simple Views" in the documentation.
If a View has one entry in its "from" list,
so it doesn't select from multiple tables,
it does not use any of these clauses:
WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET.
It does not use set operations,
which we haven't looked at
but they exist and they allow you
to use tables as if they were sets, essentially
Does not use select aggregates, window functions,
or set-returning functions.
Then what you have is a simple View
so clearly these here are not so simple
and what that means is these that are not simple
are not updatable.
Let's talk about what that means.
Let's imagine we've got this dataset of employees.
We've got the IDs, their names,
their salaries, and the department.
A useless View might be this one.
"CREATE VIEW, useless employee View
AS SELECT star FROM employees.".
Not terribly useful but you can see
that it is a simple View.
It doesn't have any of the things
that we discussed a moment ago.
It has a simple entry in its "from" list,
it's not using any set operations
or any aggregates or anything like that.
What this means is that because it's a simple View
it's an updatable View
and you can therefore insert into it
if so you desire.
Now, again not a terribly useful one here
but you can see that a View is not just a name for a query.
Behind the scenes it's more than that
and therefore you can insert into it if you want.
You can have Views with "where"
and they can still be simple Views.
So you can create a View of high earners,
"WHERE SELECT star from employees",
where they have a high salary of 8000 or more.
You can still insert into this View,
even without following the "where"
and that means that you can do "insert" into high earners,
somebody that's not a high earner.
So this might look a bit weird.
Right, we've got here our simple View
and we're doing this "where" clause
which means whenever we select from this View,
we're only gonna get people with 8000 or more as a salary
but you can still insert people
that don't have a high salary into this View.
What happens if you do that is essentially,
just goes into the table
and if you select from the View again
this row would not appear in the "select"
but you can insert.
What you can't do though with a View
is you can't update or delete rows
that are not in the View.
So essentially lets you manipulate the View
without really going into danger zone
of potentially deleting or changing data
from the View because you've got
this "where" clause in there.
You can avoid the ability to do that
if you use local check option.
So if you define a View with local check option
then you can't do anything that violates its definition,
for example if we have this create View high earners
as the same stuff but we add with local check option
that tells Postgre that whenever we try to do anything
that modifies this View
it's gonna check the "where" clause here
and see if that's permitted.
So if you try to do something like update high earners,
"SET salary equal 5000 WHERE ID equals six",
you're not gonna be able to do that.
If you try to insert something that doesn't fit
the "where" clause then you're not
gonna be able to do that either and that's that.
However, using local check option
means that you have to follow the View's definition
but you don't have to follow sub-View definitions.
And for that we have cascaded check option.
So let's look at an example for that.
Here we've got high earners
where the salary is greater than 8000.
This is the base View and has no checks.
Here we've got the high salary researchers
which select from high earners
but the department must be research
and this has local check option.
So if you try to insert into here,
you can't insert "product" employees
but you can insert "low earners"
because this View is only checking the local "where" clause.
It's not checking the sub-View "where" clause.
Finally, if you have a View
of high salary researchers for example,
that does the same thing but with a cascaded check option.
Then you can only insert research employees and high earners
because this one is gonna check this "where" clause
and then it's gonna go down to the sub-View and check that
and if there were more cascaded Views
then it would check those as well.
You can also see from here that you can build
a hierarchy of Views
but remember every time you select in here from a View,
you're running its query.
So if you have some part of your application accessing
high salary researchers you are actually
doing a "select" of high salary researchers
and a "select" of high earners
and a "select" from employees,
so if you do a lot of these
it can potentially become a bit slower.
So use Views for like really complicated stuff
that you don't wanna be repeating over and over,
not necessarily for really simple stuff.
What are non-updatable columns?
These are columns that are generated
and they can't be changed in the View.
For example, columns that are a result of a sub-query.
If you have something like this View,
"projects per high earner",
where that is the "find" as employees dot star,
everything the employees table,
and the count of projects where the employee ID
matches the outer table's ID as project count.
This here is a generated column
because it's not part of the table where we selected,
therefore if you try to "insert" or update a View,
this View, you're not going to be able to affect
the generated column but you will still be able to insert
or update things from the employees table.
So that's something to take into account with Views,
you can still use it as a normal View,
you just can't affect the generated columns.
So this was a quick primer on Views.
Essentially, as I've been saying,
Views are names for queries
and you can use them as tables in some places
and it's worth looking at the official documentation
for more examples and more edge cases
'cause there's quite a lot of them
but as well as Views, which are useful but you know,
they have their limitations.
Especially the fact that the query has to run every time.
You have something called materialised Views.
And, naturally, earlier I said that the View
where you have to execute the query
every time is not materialised.
Clearly materialised Views are the ones
where you don't execute every time.
The data is saved to the database permanently,
so accessing the View does not rerun the query.
That does mean you can have stale data in your query
because the View does not run the query.
So you can manually refresh the View whenever you want
so that the query does re-run.
You can create a materialised View
the same way as you do a view,
but you just put the materialised key word in there
and whenever you wanna refresh it,
you just say "REFRESH MATERIALISED VIEW"
and you give it a name and that's gonna re-run the query
and update the table that creates behind the scenes.
Materialised Views are pretty good
for when you don't really care
about having the latest data at all times
and you want speed, especially if your Views are slow.
You can use a materialised View
and that is gonna be much faster to access
but of course it's gonna take up
more disc space in exchange
because you actually have to store
the data in the disc.
So something good to remember for when
you have long, complicated,
computationally intensive Views
but you're not that concerned
about always having the latest data,
you can use a materialised View for that.
All right, thank you guys for joining me in this video,
I hope you've learned something about Views and mat-Views.
I'll see you in the next one.