Managing views

Database Design

Lis Sulmont

Curriculum Manager

Creating more complex views

Aggregation: SUM(), AVG(), COUNT(), MIN(), MAX(), GROUP BY, etc
Joins: INNER JOIN, LEFT JOIN. RIGHT JOIN, FULL JOIN
Conditionals: WHERE, HAVING, UNIQUE, NOT NULL, AND, OR,>,<, etc

Granting and revoking access to a view

GRANT privilege(s) or REVOKE privilege(s)

ON object

TO role or FROM role

Privileges: SELECT, INSERT, UPDATE, DELETE, etc
Objects: table, view, schema, etc
Roles: a database user or a group of database users

Granting and revoking example

GRANT UPDATE ON ratings TO PUBLIC;

REVOKE INSERT ON films FROM db_user;

Updating a view

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Not all views are updatable

View is made up of one table
Doesn't use a window or aggregate function

¹ https://www.postgresql.org/docs/9.5/sql-update.html

Inserting into a view

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

Not all views are insertable

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

Inserting into a view

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

Not all views are insertable

Takeaway: avoid modifying data through views

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

Dropping a view

DROP VIEW view_name [ CASCADE | RESTRICT ];

RESTRICT (default): returns an error if there are objects that depend on the view
CASCADE: drops view and any object that depends on that view

Redefining a view

CREATE OR REPLACE VIEW view_name AS new_query

If a view with view_name exists, it is replaced
new_query must generate the same column names, order, and data types as the old query
The column output may be different
New columns may be added at the end

If these criteria can't be met, drop the existing view and create a new one

¹ https://www.postgresql.org/docs/9.2/sql-createview.html

Altering a view

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

¹ https://www.postgresql.org/docs/9.2/sql-alterview.html

Let's practice!

Database Design