SQL Views

Common Challenges

• Scenario 1: Complexity
• Imagine a very complex query (multiple joins, filters, calculations) needed for several different reports. How do you avoid repeating this logic?
• Scenario 2: Security/Access Control
• Imagine needing to show users some data from a table (e.g., employee names, departments) but hide sensitive columns (e.g., salary). How can this be managed securely?

Building Complex Queries

• Key elements included:
• Selecting specific columns.
• Filtering rows using WHERE.
• Combining data from multiple tables using JOIN (INNER, LEFT, etc.).
• Ordering results using ORDER BY.
• These queries can become lengthy and complex.

Today's Topic: SQL Views

• Solution: SQL Views provide a mechanism to handle the previously mentioned challenges.
• Definition: A View is essentially a saved SELECT query stored in the database schema.
• Analogy: Think of a View as a virtual table – it looks and acts like a table when queried, but its contents are generated dynamically from its underlying query.
• Focus: We will explore Views specifically within the PostgreSQL RDBMS.

Learning Objectives

• By the end of this lecture, you will be able to:
• Define what a SQL View is and its purpose.
• Explain the benefits of using Views (Simplicity, Security, Logical Independence).
• Create, query, modify, and drop standard Views in PostgreSQL.
• Understand the concept of View updatability and its limitations.
• Define Materialized Views in PostgreSQL and understand their use cases and management (creation, refresh).

What is a SQL View? Definition

• A SQL View is a virtual table whose contents are defined by a stored SQL query.
• The query defining the view can access data from one or more underlying base tables or even other views.
• It is stored within the database schema as a named object.

What is a SQL View? Analogy & Concept

• Analogy: Think of a view as a precisely defined "window" looking onto your database tables.
• You choose what data is visible through the window (columns).
• You choose which specific items are visible (rows based on filters).
• You might combine views from multiple sources (joins).
• The view itself is just the frame and the specification of what to look at, not the items themselves.

Key Concept: Views Store Definitions, Not Data

• Crucial Point: Standard SQL Views do not physically store data.
• They store the SELECT statement that defines them.
• Execution Flow:
1. You write a query against a view (SELECT * FROM my_view).
2. The database system retrieves the view's stored SELECT query.
3. The database system executes this underlying query against the base tables at that moment.
4. The results of the underlying query are returned to you as if they came from the view itself.

Views vs. Base Tables

• Base Tables:
• Physically store the actual data rows on disk.
• Defined with CREATE TABLE.
• Represent the primary storage of information.
• Views:
• Do not store data (standard views).
• Defined with CREATE VIEW.
• Derive their content dynamically from base tables (or other views) via a stored query.
• Provide a logical representation or abstraction of data.

Why Use Views? Benefits & Use Cases

• Views offer several advantages in database design and application development.
• Key benefits include:
1. Simplicity
2. Security / Access Control
3. Logical Data Independence

Benefit 1: Simplicity

• Hide Complexity: Encapsulate intricate SELECT statements (complex joins, aggregations, calculations, filters) behind a simple view name.
• Intuitive Structure: Present data in a format tailored to specific user groups or applications, making it easier to understand and query.
• Example: Instead of writing SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.id; repeatedly, create a view:

CREATE VIEW v_employee_department AS

SELECT e.name AS employee_name, d.name AS department_name

FROM employees e JOIN departments d ON e.dept_id = d.id;

• Users can then simply query:

SELECT * FROM v_employee_department;

Benefit 2: Security / Access Control

• Views act as a security layer by controlling data visibility without granting direct table access.
• Column-Level Security: Expose only specific, non-sensitive columns.
• Example: Create a view v_public_employee_info that excludes the salary column from the employees table. Grant access to this view, not the base table.

CREATE VIEW v_public_employee_info AS

SELECT id, name, dept_id FROM employees;

• Row-Level Security: Filter rows based on certain criteria (e.g., user role, department).
• Example: A view showing only employees in the 'Sales' department.

CREATE VIEW v_sales_staff AS

SELECT id, name FROM employees WHERE dept_id = (SELECT id FROM departments WHERE name = 'Sales');

Benefit 3: Logical Data Independence

• Stable Interface: Views provide a consistent structure to applications and users, even if the underlying base table schema changes.
• Decoupling: Applications interact with the view's defined columns. Changes to base tables (like splitting tables, renaming columns) can potentially be masked by modifying the view's definition.

Benefit 3: Logical Data Independence

• Example:
• Initially, employees table has id, name, address. A view v_employee_address selects these.
• Later, address is moved to a separate employee_addresses table (emp_id, street, city).
• The view v_employee_address can be updated (CREATE OR REPLACE VIEW) to join employees and employee_addresses to return the same id, name, address structure.
• Applications querying the view require no changes (assuming the view's output remains compatible).
• Note: This independence has limits; significant schema changes might still require application updates.

​

Working with Standard Views: Creating Views

• Use the CREATE VIEW statement to define a new view.
• Syntax:

CREATE VIEW view_name [(column_list)]

AS

SELECT_statement;

• view_name: The unique name for your view.
• (column_list): Optional list to explicitly name the view's columns. If omitted, column names are derived from the SELECT statement.
• SELECT_statement: The query that defines the data the view will represent. Can be simple or complex.

Creating Views: PostgreSQL Example

• Scenario: Create a view showing employee names and their department names, specifically for the 'Sales' department.
• Assumed Tables:
• employees (id, name, dept_id, salary)
• departments (id, name)
• SQL Command:

CREATE VIEW v_employee_dept_names AS

SELECT

e.name AS employee_name, -- Alias for clarity

d.name AS department_name -- Alias for clarity

FROM

employees e

JOIN

departments d ON e.dept_id = d.id

WHERE

d.name = 'Sales';

• This creates a view named v_employee_dept_names.

Working with Standard Views: Querying Views

• Once created, you query a view exactly like you query a regular table.
• Syntax:

SELECT columns

FROM view_name

[WHERE condition]

[ORDER BY ...];

• The database replaces view_name with its underlying SELECT statement during query execution.

Querying Views: PostgreSQL Example

• Scenario: Retrieve all employee names from the v_employee_dept_names view (which only includes Sales staff) and order them.
• SQL Command:

SELECT employee_name

FROM v_employee_dept_names

ORDER BY employee_name;

• Behind the scenes: PostgreSQL effectively runs the view's definition query and then applies the outer query's SELECT and ORDER BY clauses.

Working with Standard Views: �Modifying Views

• To change the definition (the underlying SELECT statement) of an existing view, use CREATE OR REPLACE VIEW.
• This command either creates the view (if it doesn't exist) or replaces the existing view definition with the new one.
• Syntax:

CREATE OR REPLACE VIEW view_name [(column_list)]

AS

new_SELECT_statement;

• Note: PostgreSQL also offers ALTER VIEW for renaming (RENAME TO), but CREATE OR REPLACE is standard for changing the core query.

Modifying Views: PostgreSQL Example

• Scenario: Modify the v_employee_dept_names view to include employees from all departments, not just 'Sales'.
• SQL Command:

CREATE OR REPLACE VIEW v_employee_dept_names AS

SELECT

e.name AS employee_name,

d.name AS department_name

FROM

employees e

JOIN

departments d ON e.dept_id = d.id;

-- Removed the WHERE clause

• The view v_employee_dept_names now reflects data from all departments. Any existing permissions on the view remain.

Working with Standard Views: Dropping Views

• To remove a view definition from the database, use the DROP VIEW statement.
• Syntax:

DROP VIEW [IF EXISTS] view_name [, ...] [CASCADE | RESTRICT];

• IF EXISTS: Optional clause to prevent an error if the view doesn't exist.
• CASCADE: Automatically drops objects that depend on the view (e.g., other views). Use with caution.
• RESTRICT: (Default) Prevents dropping the view if any other objects depend on it.

Dropping Views: PostgreSQL Example

• Scenario: Remove the v_employee_dept_names view.
• SQL Command (Safe version):

DROP VIEW IF EXISTS v_employee_dept_names RESTRICT;

• This attempts to drop the view.
• IF EXISTS avoids an error if it's already gone.
• RESTRICT ensures it fails if another view, function, etc., relies on it.
• SQL Command (Forced version - use carefully):

DROP VIEW v_employee_dept_names CASCADE;

• This will drop the view and any dependent objects.

Updatability of Views: The Concept

• We know we can SELECT from views as if they were tables.
• Question: Can we also perform Data Modification Language (DML) operations like INSERT, UPDATE, or DELETE directly on a view?
• Answer: Sometimes. It depends on the complexity of the view's definition.

Automatically Updatable Views �("Simple Views")

• In PostgreSQL, a view is generally automatically updatable if it meets these conditions:
• References exactly one table in its FROM clause (cannot be another non-updatable view).
• Does not use GROUP BY or HAVING clauses.
• Does not use aggregate functions (e.g., COUNT(), SUM(), AVG()).
• Does not use DISTINCT.
• Does not use set operations (UNION, INTERSECT, EXCEPT).
• Essentially, each row in the view must map directly to exactly one row in the underlying base table.

Non-Updatable Views ("Complex Views")

• Views that violate the "simple view" conditions are generally not automatically updatable.
• Why? Ambiguity.
• Joins: If a view joins multiple tables, how does an INSERT distribute data? Which table does an UPDATE target if columns come from different tables? How does DELETE work across related tables?
• Aggregation (GROUP BY, aggregates): View rows represent multiple base table rows. How would you UPDATE or DELETE a single aggregate value? How would INSERT work?
• DISTINCT: Removes duplicate rows, making the mapping back to specific base table rows ambiguous.
• Attempting DML on such views usually results in an error.

Updatability Example: Simple View

• Scenario: Create a view showing only employees in the 'Sales' department.
• SQL (Create View):

​

CREATE OR REPLACE VIEW v_sales_employees AS

SELECT id, name, salary, dept_id

FROM employees

WHERE dept_id = 1;

​

This view references only the employees table and has no aggregation, joins, etc. It should be updatable.

Updating a Simple View

• Scenario: Give a 10% raise to employee with ID 123 (assuming they are in Sales).
• SQL (Update via View):

UPDATE v_sales_employees

SET salary = salary * 1.10

WHERE id = 123;

• Expected Result: Success. The update is unambiguous and directly translates to an update on the underlying employees table for the row where id = 123 and dept_id = 1.

Updatability Example: �Non-Updatable Complex View

• Scenario: Attempt to insert data using the v_employee_dept_names view (which joins employees and departments).
• Recall View Definition:

CREATE OR REPLACE VIEW v_employee_dept_names AS

SELECT e.name AS employee_name, d.name AS department_name

FROM employees e JOIN departments d ON e.dept_id = d.id;

• SQL (Attempt Insert):

INSERT INTO v_employee_dept_names (employee_name, department_name)

VALUES ('New Hire', 'Sales');

• Expected Result: Failure. PostgreSQL will likely raise an error similar to:
• ERROR: cannot insert into view "v_employee_dept_names"
• DETAIL: Views containing multiple tables are not automatically updatable.

Making Complex Views Updatable (Advanced)

• What if you need to perform DML on a complex view?
• PostgreSQL (and standard SQL) provides Triggers, specifically INSTEAD OF triggers for views.
• Concept:
• You define a trigger function that specifies exactly how an INSERT, UPDATE, or DELETE operation on the view should be translated into actions on the underlying base tables.
• The database executes your trigger function instead of trying (and failing) to update the view directly.
• Note: Writing trigger functions requires procedural language programming (e.g., PL/pgSQL) and careful logic.

Beyond Standard Views: Materialized Views

• PostgreSQL offers another type of view: Materialized Views.
• These differ significantly from standard views in how they handle data.
• Standard View: Stores only the SELECT query definition. The query runs every time the view is accessed. Data is always "live" (reflects current base table state).
• Materialized View: Executes the SELECT query and stores the resulting data physically, similar to a table snapshot.

Why Use Materialized Views? Performance

• Primary Goal: Improve query performance.
• Use Case: Ideal for complex, resource-intensive, or slow-running queries that are accessed frequently.
• Examples: Complex reports, data summaries, dashboards, data warehousing aggregations.
• Scenario: If a query takes minutes to run, running it repeatedly via a standard view is inefficient. A materialized view runs the query once (at refresh) and stores the results for fast retrieval.
• Trade-off: Performance gain comes at the cost of data potentially being stale.

Creating Materialized Views

• Syntax is similar to standard views, but uses CREATE MATERIALIZED VIEW.
• Syntax:

CREATE MATERIALIZED VIEW view_name

AS

SELECT_statement

[WITH [NO] DATA]; -- Default is WITH DATA (populate at creation)

• The SELECT_statement can be complex (joins, aggregation, etc.).
• The results of the query are calculated and stored when the command is executed (if WITH DATA is used).

Creating Materialized Views: Example

• Scenario: Create a materialized view to store summary statistics (average salary, employee count) for each department. This might be slow to calculate on the fly for large tables.
• SQL Command:

CREATE MATERIALIZED VIEW mv_department_salary_summary AS

SELECT

d.name AS department_name,

AVG(e.salary) AS avg_salary,

COUNT(e.id) AS num_employees

FROM

employees e

JOIN

departments d ON e.dept_id = d.id

GROUP BY

d.name;

• This executes the query and stores the aggregated results in mv_department_salary_summary.

Refreshing Materialized Views: The Catch!

• CRUCIAL POINT: Data in a materialized view does not automatically update when the underlying base tables change. It's a snapshot.
• You must explicitly refresh the view to update its stored data.
• Syntax:

REFRESH MATERIALIZED VIEW view_name;

• Executing REFRESH re-runs the view's defining SELECT query and replaces the old stored data with the new results. This can be resource-intensive.

Refreshing Materialized Views: Stale Data Trade-off

• The Trade-off:
• Benefit: Fast queries against the materialized view (reading stored data).
• Cost: Data can be stale (out of sync with base tables) between refreshes.
• Managing Freshness:
• Determine acceptable data latency (how old can the data be?).
• Schedule REFRESH MATERIALIZED VIEW commands accordingly (e.g., using cron jobs, pg_cron, or application logic). Common schedules include nightly, hourly, or based on specific events.

Querying and Dropping Materialized Views

• Querying:
• You query a materialized view exactly like a standard view or a table.

SELECT * FROM mv_department_salary_summary WHERE avg_salary > 60000;

• Queries read the stored data, making them fast.
• Dropping:
• Use the DROP MATERIALIZED VIEW command.
• Syntax:

DROP MATERIALIZED VIEW [IF EXISTS] view_name;

• This removes the view definition and its stored data.

Lecture Summary: Key Concepts

• SQL Views: Named, stored SELECT queries that act like virtual tables.
• Core Purpose: Provide abstraction over underlying data structures.
• Key Benefits:
• Simplicity: Hide complex query logic.
• Security: Restrict row and column access.
• Logical Data Independence: Stable interface despite potential schema changes.

Summary: Standard Views

• Data Storage: Store only the query definition, no physical data.
• Execution: The defining query is executed each time the view is accessed. Data is always current relative to base tables.
• Key Commands (PostgreSQL):

CREATE VIEW view_name AS ...

CREATE OR REPLACE VIEW view_name AS ... (Modify definition)

SELECT ... FROM view_name ... (Querying)

DROP VIEW [IF EXISTS] view_name [CASCADE | RESTRICT]

Summary: View Updatability

• INSERT, UPDATE, DELETE operations on views are possible only under specific conditions.
• Generally Updatable: "Simple" views based on a single table without aggregation, DISTINCT, complex functions, etc.
• Generally Not Updatable: Complex views involving joins, GROUP BY, aggregates, etc. (Ambiguity).
• Advanced: Triggers can provide custom update logic for complex views (requires programming).

Summary: Materialized Views

• Data Storage: Store the physical results (snapshot) of the query.
• Execution: Query runs only at creation and during refresh. Queries against the view read stored data (fast).
• Use Case: Performance boost for complex, frequently accessed queries where some data latency is acceptable.
• Key Commands (PostgreSQL):

CREATE MATERIALIZED VIEW mv_name AS ...

REFRESH MATERIALIZED VIEW mv_name; (Crucial for updating data)

SELECT ... FROM mv_name ... (Querying)

DROP MATERIALIZED VIEW [IF EXISTS] mv_name;