GoshiX's Site

Представления (Views).

1. Tеоретическая справка.

Представления (Views): – это виртуальная таблица, содержимое которой (столбцы и строки) определяется запросом.

Представление — это виртуальная (логическая) таблица, представляющая собой поименованный запрос (синоним к запросу), который будет подставлен как подзапрос при использовании представления. Используется, если необходимо часто делать какой-то запрос со сложной логикой.

Не является самостоятельной частью набора данных
Вычисляется динамически на основании данных, хранящихся в реальных таблицах
Изменение данных в таблицах немедленно отражается в содержимом представлений

Представление можно использовать в следующих целях:

Для направления, упрощения и настройки восприятия информации в базе данных каждым пользователем.
В качестве механизма безопасности, позволяющего пользователям обращаться к данным через представления, но не дающего им разрешений на непосредственный доступ к базовым таблицам.

Достоинства:

Безопасность: можно искусственно ограничивать информацию, к которой у пользователя есть доступ.
Простота запросов: при написании запросов обращаемся к вью, как и к обычной таблице.
Защита от изменений: пользователю не обязательно знать, что структуры / имена таблиц поменялись. Достаточно обновить представление.

Недостатки:

Производительность: кажущийся простым запрос с использованием вью на деле может оказаться очень сложным из-за логики, “зашитой” во вью.
Управляемость: вью может быть основана на вью, которая в свою очередь тоже основана на другой вью и т.д.
Ограничение на обновление:* не любую вью можно обновить, что не всегда очевидно пользователю.

Синтаксис:

CREATE
[ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] 
  VIEW name [ ( column_name [, ...] ) ]
  [
WITH (view_option_name [= view_option_value] [, ...]) ]
  AS query
  [
WITH [ CASCADED | LOCAL ] CHECK
OPTION ]

CREATE VIEW – создание нового представления.
CREATE OR REPLACE VIEW – создание или замена уже существующего представления.
- В случае замены в новом представлении должны присутствовать все поля старого представления (имена, порядок, тип данных). Допустимо только добавление новых полей.
TEMPORARY | TEMP – временное представление, будет существовать до конца сессии.
view_name – название представления.
column_name – список полей представления. Если не указан, используются поля запроса.
query – SELECT или VALUES команды.

Изменения представления:

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 [, ... ] )
DROP VIEW [IF EXISTS] name [, ...] [ CASCADE | RESTRICT ]

IF EXISTS - Не бросать ошибку, если представление не существует.
CASCADE - Автоматически удалять объекты, зависящие от данного представления (например, другие представления).
RESTRICT - Не удалять объекты, зависящие от данного представления. (По умолчанию)

Примеры:

Создание представления

-- naming:
-- для удобства добавляем суффикс _v
CREATE VIEW greeting_v AS
SELECT 'Hello World';

CREATE VIEW greeting_v AS
SELECT text 'Hello World' AS hello;


CREATE VIEW comedies_v AS
SELECT 
    *
FROM 
    films
WHERE 
    kind = 'Comedy';

Если после создания представления добавить столбцы в таблицу, в представлении их не будет.

Создание представления как результата сложного запроса:

-- teacher - таблица преподавателей
SELECT last_name AS name, id_org
FROM teacher;

-- Ответ:
/*
name              |id_org|
------------------+------+
Халяпов Александр |     2|
Меркурьева Надежда|     1|
Иванов Иван       |     3|
Роздухова Нина    |     1|
Петров Петр       |     3|
*/

SELECT id_org, name_org
FROM organization;
-- Ответ:
/*
id_org|name_org        |
------+----------------+
     3|Сбер            |
     1|АО Тинькофф Банк|
     2|X5 Retail Group |
*/

-- создадим представление, где для каждого преподавателя
-- видно название соответствующей компании
CREATE OR REPLACE VIEW teacher_org_v
SELECT teacher_name AS name, name_org
FROM
    teacher
JOIN
    organization
USING (id_org);

SELECT *
FROM teacher_org_v;
-- Ответ:
/*
name              |name_org        |
------------------+----------------+
Петров Петр       |Сбер            |
Иванов Иван       |Сбер            |
Роздухова Нина    |АО Тинькофф Банк|
Меркурьева Надежда|АО Тинькофф Банк|
Халяпов Александр |X5 Retail Group |
*/


-- Аналогично с GROUP BY

TEMP или TEMPORARY

Представление создаётся как временное. Удаляется при окончании сессии

CREATE TEMP VIEW greeting AS
SELECT 'Hello World';

MATERISALISED

CREATE MATERIALIZED VIEW — создать материализованное представление

Синтаксис

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

IF NOT EXISTS - Не бросать ошибку, если представление не существует.
USING method - Это дополнительное предложение задаёт табличный метод доступа, который будет использоваться для сохранения содержимого нового материализованного представления; этот метод должен быть типа TABLE
WITH ( параметр_хранения [= значение] [, ... ] ) - Задаёт дополнительные параметры хранения для создаваемого материализованного представления
TABLESPACE tablespace_name - Здесь tablespace_name — имя табличного пространства, в котором будет создано материализованное представление. Если оно не задано, выбирается default_tablespace.
WITH [ NO ] DATA - Указывает, будет ли материализованное представление наполняться в момент создания. Если материализованное представление не наполняется, оно помечается как нечитаемое, так что к нему нельзя будет обращаться до выполнения REFRESH MATERIALIZED VIEW.

Описание:

CREATE MATERIALIZED VIEW определяет материализованное представление запроса. Заданный запрос выполняется и наполняет представление в момент вызова команды (если только не указано WITH NO DATA). Обновить представление позже можно, выполнив

REFRESH MATERIALIZED VIEW table_name;

Команда CREATE MATERIALIZED VIEW подобна CREATE TABLE AS, за исключением того, что она запоминает запрос, порождающий представление, так что это представление можно позже обновить по требованию. Материализованные представления сходны с таблицами во многом, но не во всём; например, не поддерживаются временные материализованные представления и автоматическая генерация OID.

RECURSIVE

Представление создаётся как рекурсивное. Эквивалентные формы:

CREATE RECURSIVE VIEW [ schema.
] view_name (column_names
) AS
SELECT ...;

CREATE VIEW [ schema.
] view_name AS
WITH RECURSIVE view_name (column_names) AS (SELECT ...)
SELECT column_names
FROM view_name;

Рекурсивное представление – пример 1 (числа от 1 до 100):

CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
VALUES (1)
UNION ALL
SELECT
    n + 1
FROM 
    nums_1_100
WHERE 
    n < 100;

пример 2 ($a_n = 3 * a_{n - 1} + 5$):

CREATE OR REPLACE RECURSIVE VIEW recursive_fn(n, a_n) AS
(
    SELECT
        0 AS n,
        4 AS a_n

    UNION ALL

    SELECT
        gp.n + 1 AS n,
        gp.a_n * 3 + 5 AS a_n
    FROM
        recursive_fn gp
    WHERE
        gp.n < 9
);

Типы представлений:

горизонтальное — ограничение данных по строкам:

CREATE VIEW V_IT_EMPLOYEE AS
SELECT 
    *
FROM 
    EMPLOYEE
WHERE 
    DEPARTMENT_NM = ‘IT’;

вертикальное — ограничение данных по столбцам:

CREATE VIEW V_EMP AS
SELECT 
    EMP_NM, 
    DEPARTMENT_NM
FROM 
    EMPLOYEE;

Обновляемые представления

Представление называется обновляемым, если к нему применимы операции UPDATE и DELETE для изменения данных в таблицах, на которых построено это представление.

Требования:

Ровно 1 источник в предложении FROM, являющийся таблицей или обновляемым представлением
Запрос не должен содержать WITH, DISTINCT, GROUP BY, HAVING, LIMIT или OFFSET
Запрос не должен содержать операторов UNION, INTERSECT или EXCEPT
select-list запроса не должен содержать агрегатных, оконных, а также функций, возвращающих множества.

WITH [ CASCADED | LOCAL ] CHECK
OPTION

Задаёт поведение обновляемым представлениям: проверки, не позволяющие записывать данные, невидимые через представление

LOCAL – проверки выполняются только на самом представлении
CASCADED – проверки выполняются и на самом представлении, и на источнике, и так далее по цепочке обращений

Обновляемые представления – пример 1:

CREATE VIEW universal_comedies AS
SELECT 
    *
FROM 
    comedies
WHERE 
    classification = 'U'
WITH LOCAL CHECK OPTION;

Попытка вставить или отредактировать ряд с classification <> 'U' приведёт к ошибке.
Но при этом вставка или редактирование ряда с kind <> 'Comedy' будет успешной.

Обновляемые представления – пример 2:

CREATE VIEW universal_comedies AS
SELECT 
    *
FROM 
    comedies
WHERE 
    classification = 'U'
WITH CASCADED CHECK OPTION;

Попытка вставить или отредактировать ряд с classification <> 'U' или kind <> 'Comedy' приведёт к ошибке.

Столбцы в обновляемом представлении могут быть как обновляемые, так и не обновляемые.

Обновляемые представления – пример 3:

CREATE VIEW comedies AS
SELECT 
    f.*,
    country_code_to_name(f.country_code) AS country,
    (SELECT avg(r.rating) FROM user_ratings r WHERE r.film_id = f.id) AS avg_rating
FROM 
    films f
WHERE 
    f.kind = 'Comedy';

Все столбцы таблицы films – обновляемые. Столбцы country и avg_rating – readonly.

Если представление не удаётся сделать обновляемым, но в этом есть потребность – используйте INSTEAD OF триггер.

Это такая функция, которая будет обрабатывать операции модификации данных – рассмотрим позже.

Примечание!

Активное использование представлений — это ключевой аспект хорошего проектирования баз данных SQL. Представления позволяют вам скрыть внутреннее устройство ваших таблиц, которые могут меняться по мере развития приложения, за надёжными интерфейсами.

Представления можно использовать практически везде, где можно использовать обычные таблицы. И довольно часто представления создаются на базе других представлений.

2. Практическое задание (Views)

Даны две таблицы, с организациями и преподавателями, которые ведут лекции и семинары в МФТИ. Требуется создать эти таблицы и различные view, связанные с ними.

CREATE SCHEMA sem_view;

DROP TABLE IF EXISTS sem_view.organization;
CREATE TABLE sem_view.organization AS
SELECT
    1 AS id_org,
    'АО Тинькофф Банк' AS name_org
UNION
SELECT
    2,
    'X5 Retail Group'
UNION
SELECT
    3,
    'Сбер';

CREATE TABLE sem_view.teacher (id_teach, last_name, first_name, birth_date, salary_amt, id_org) AS
SELECT
    1, 'Роздухова', 'Нина', '1992-04-15', 15000.00, 1
UNION
SELECT
    2, 'Меркурьева', 'Надежда', '1995-03-12', 25000.00, 1
UNION
SELECT
    3, 'Халяпов', 'Александр', '1994-09-30', 17000.00, 2
UNION
SELECT
    4, 'Иванов', 'Иван', NULL, 100000.00, 3
UNION
SELECT
    5, 'Петров', 'Петр', NULL, 3000.00, 3;

Задание:

Создать view – полную копию таблицы teacher;
Создать view – копию таблицы teacher, за исключением строк, у которых нет связи с организацией;
Создать view с полным списком преподавателей. Вместо id организации выводить ее название. Не включать в представление зарплату преподавателя;
Создать view с полным списком преподавателей аналогично пункту (3). Фамилию и имя преподавателя объединить в одно поле. Поля назвать соответственно русским названиям – «Фамилия Имя», «Дата рождения», «Название организации»;
Написать вставку записи (на своё усмотрение) во view из пункта (1). Проверить, что новая запись появилась в исходной таблице;
Написать удаление записи, вставленной в пункте (5), через view из пункта (1). Проверить, что запись удалилась из исходной таблицы;
Обновить дату рождения и у преподавателя id_teach = 4 (на любую) через view из пункта (1);
Обновить id_org у преподавателя с id_teach = 4 на NULL через view из пункта (2). Проверить, что преподаватель пропал view из пункта (2);
Пересоздать view и пункта (2) с условием [with local check option]. Попробовать проделать те же манипуляции, что в пункте (8) на преподавателе id_teach = 5.
Поверх view из предыдущего пункта создать view с [with cascaded check option]. Проверить, что то же действие всё ещё кидает ошибку.
Создать view со статистикой зарплат по организациям: среднее по организации и отличие от среднего, используя оконную функцию.
Создать view с учителями с зарплатой не ниже средней по организации (используте вложенный запрос) (те, у кого не указана организация считаются в одной организации).

(bonus) А теперь потренируемся с рекурсией: посчитаем числа Каталана:

Зададим рекурренту:

$C_0 = 1$, $C_n = \sum_{i=1}^{n} C_{i} + C_{n-i-1}$

Которая выражается так:

$C_0 = 1$, $C_n = \frac{2(2n - 1)}{n + 1}C_{n - 1}$

Цель: создать рекурсивное представление для вычисления всех $C_k$, $k < n$.

Представления (Views).

1. Tеоретическая справка.

2. Практическое задание (Views)

Полезные ссылки