A view is a database object that is a stored query. A view is a virtual table you can create dynamically using a saved query acting as a virtual table.
You can join a view to another table or view
You can query a view
Regular views don't store any data, but materialised view does
Syntax
CREATE OR REPLACE VIEW view_name AS query
Example 1
CREATE OR REPLACE VIEW v_movie_quick AS
SELECT movie_name,
movie_length,
release_date
from movies mv;
-- use just like a normal table
select * from v_movie_quick limit 5;
movie_name | movie_length | release_date
--------------------+--------------+--------------
A Clockwork Orange | 112 | 1972-02-02
Apocalypse Now | 168 | 1979-08-15
Battle Royale | 111 | 2001-01-04
Blade Runner | 121 | 1982-06-25
Chungking Express | 113 | 1996-08-03
Example with JOIN
CREATE OR REPLACE VIEW v_movie_d_name as
SELECT movie_name,
movie_length,
release_date,
d.first_name || ' ' || d.last_name as "full name"
from movies mv
inner join directors d
on mv.director_id = d.director_id;
select * from v_movie_d_name limit 5;
movie_name | movie_length | release_date | full name
------------------------+--------------+--------------+-----------------
Let the Right One In | 128 | 2008-10-24 | Tomas Alfredson
There Will Be Blood | 168 | 2007-12-26 | Paul Anderson
The Darjeeling Limited | 119 | 2007-09-29 | Wes Anderson
Rushmore | 104 | 1998-11-12 | Wes Anderson
Grand Budapest Hotel | 117 | 2014-07-03 | Wes Anderson
Managing VIEW
ALTER VIEW v_movie_d_name RENAME TO v_movie_with_names;
-- ALTER VIEW
select * from v_movie_with_names limit 5;
-- SAME OUTPUT AS ABOVE
-- dropping view
DROP VIEW if exists v_movie_quick;
View containing condition
CREATE OR REPLACE VIEW v_movie_after_1997 as
select *
from movies
where release_date >= '1997-12-31'
and movie_lang = 'English'
order by release_date desc limit 5;
movie_id | movie_name | movie_length | movie_lang | release_date | age_certificate | director_id
----------+--------------------------------------------+--------------+------------+--------------+-----------------+-------------
47 | Three Billboards Outside Ebbing, Missouri | 134 | English | 2017-11-10 | 15 | 18
15 | Grand Budapest Hotel | 117 | English | 2014-07-03 | PG | 3
22 | Life of Pi | 129 | English | 2012-11-21 | PG | 15
38 | Submarine | 115 | English | 2011-06-03 | 15 | 4
24 | Never Let Me Go | 117 | English | 2010-09-15 | 15 | 25
You cannot add, update, delete columns from a view once created for that create a new view, drop the old view and rename the new view back to original
Deleting from view also deletes from the main table
CREATE OR REPLACE VIEW v_movie as
select *
from movies;
select *
from v_movie limit 5;
select *
from movies limit 5;
-- deleting from v_movie also deletes from main table
delete
from v_movie
where movie_id = 4;
Materialized View
Allows you to
store result of a query
update data periodically :: manual
used to cache result of heavy data
syntax
CREATE MATERIALIZED VIEW IF NOT EXISTS view_name
AS query WITH [ NO ] DATA;
If you want to load data into the materialised view at the creation time, you will use WITH DATA
create materialized view if not exists mv_dir as
select first_name, last_name
from directors
with no data;
-- this will give error becuz no data
-- is loaded at time of creation
select * from mv_dir limit 5;
-- refreshing view to load data in view
refresh materialized view mv_dir;
select * from mv_dir limit 5;
-- dropping materialized view
drop materialized view mv_dir;
cannot change data in materialised view : insert, update and delete
advantage of usingmaterialised view : access and update materialised view without locking everyone else out
disadvantage of using materialised view if you alter the base table ,
materialised view must also me alter : delete the old materialised view and create a new one