5.2. Представление о мире (VIEW)Представления предназначены для сохранения результатов выполнения запросов в виде таблиц. В отличие от хранимых процедур. - презентация

1 5.2. Представление о мире (VIEW)Представления предназначены для сохранения результатов выполнения запросов в виде таблиц. В отличие от хранимых процедур это не выполняемые функции, а псевдонимы запросов. Преимущества: 1. не надо отслеживать удаление и добавление данных во временную таблицу, 2. простой доступ к результатам сложного запроса, 3. возможность редактирования результатов. Формат представления: CREATE [ OR REPLACE ] VIEW AS Запрос; В других СУБД(например в MySQL) могут добавляться дополнительные опции: [DEFINER = User | CURRENT_USER] [ALGORITM = MERGE | TEMPTABLE | UNDEFINED] Для представления используется только временная таблица | Данные для представления сначала помещаются во временную таблицу, а затем выполняется инструкция (удобно для краткого снятия блокировок) | Никакое предложение по алгоритму не присутствует SELECT dsf As Дата,nsf As,summa_sf INTO $$$_sf_mts FROM sf WHERE INN Like 774%; DELETE $$$_ sf_mts.* FROM sf_mts; DROP TABLE $$$_ sf_mts; Обычные sql-запросы, не использующие представление

2 Любой добавляемый в представлении столбец должен присутствовать в базовой таблице. Изменим таблицу sf, добавив в нее массив строк: ALTER TABLE sf ADD stroki TEXT ARRAY; Изменим определяющий запрос: CREATE VIEW sf_mts AS SELECT dsf As Дата,nsf As,summa_sf FROM sf WHERE INN Like "774%"; Зададим значения массива строк в представлении sf_mts: UPDATE sf_mts SET stroka = { consalting,outsorsing } WHERE INN Like "774%"; Получим: ERROR: cannot update a view HINT: You need an unconditional ON UPDATE DO INSTEAD rule. В других СУБД(например в MySQL) могут добавляться дополнительные опции: [DEFINER = User | CURRENT_USER] [ALGORITM = MERGE | TEMPTABLE | UNDEFINED] Для представления используется только временная таблица | Данные для представления сначала помещаются во временную таблицу, а затем выполняется инструкция (удобно для краткого снятия блокировок) | Никакое предложение по алгоритму не присутствует

3

4 DROP VIEW [ IF EXISTS ] [, … ] [ RESTRICT | CASCADE ] IF EXISTS – предотвращают ошибку удаления несуществующих представлений

5 5.3. Правила (RULE). Изменить структуру таблицы sf, добавив в нее массив.

6 Есть дополнительные ограничения при замене представлений. Нельзя менять: имена, типы, последовательность полей

7 Можно обновить таблицу sf, нельзя обновить представление, созданные на на основе этой таблицы, без задания правил Правила (RULE) – это описание способа изменения порождаемого дерева запроса(которое порождается каждый раз ядром БД при выполнении команд SQL и называется абстрактным синтаксическим деревом). Операторы и значения становятся, соответственно, ветвями и листьями. Далее планировщик sql-запроса трансформирует дерево с целью оптимизации выполнения запроса. Правила позволяют переписать дерево выполнения sql-запроса, произвести операции обхода, отсечения ветвей и т.д. Это производится до отправки планировщику запроса, который потом посылает запрос на выполнение.

8 Клиент PostgreSQL Анализатор ПереписываниеПланировщик Выполнение Правила Представления Здесь запрос оптимизируется перед выполнением Модифицирует дерево запроса применяя правила Преобразует строку sql-команды в дерево запроса Сервер PostgreSQL Дерево запроса Новое дерево запроса Деревья запросов Определяет пользователь Результаты Строка sql- команды

9 Команда просмотра плана выполнения запроса EXPLAIN Filter заменяет WHERE Output заменяет Cписок столбцов

10 Создадим правила, затем выполним связанный с ним запрос обновления. Правила должны объяснить, что делать с командой UPDATE: правило должно для всех обновлений представления sf_mts_1 предварительно применять их к таблице sf, интерпретируя New как отношение, содержащее новое значение, а Old – как отношение содержащее значения до обновления Формат: CREATE OR REPLACE RULE Имя-Правила AS ON UPDATE Имя-Представления DO INSTEAD Запрос-по-источнику 1. Создать правило 2. Выполнить представление

11 Результаты:

12 5.4. Перекрестные запросы (сводные таблицы (pivot table) с помощью crosstab() Главной особенностью перекрестных запросов является дополнительная возможность «свести» сгруппированные данные одной операции относительно результатов другой групповой операции. Сводные таблицы строятся с помощью функции crosstab(). В СУБД PostdreSQL вместо visual-basic- функции Format$(…) для выделения отдельных компонент даты и времени используется функция extract( ): SELECT extract(year from dsf) AS Year, extract(month from dsf) AS Month, inn, count(*) FROM sf GROUP BY Year, Month, inn; Функция crosstab() требует обязательного наличия трех следующих по содержанию столбцов: 1. Идентификатора строки (их можно быть много, min=1), 2. Идентификатора категория (только один столбец), 3. Значения (только один столбец). Создадим и заполним таблицу: CREATE TEMPORARY TABLE my_month (month INT); Добавим в нее записи: INSERT INTO my_month VALUES (1),(2),(3),(4), (5),(6), (7),(8),(9),(10),(11),(12); Выполним запрос: SELECT * FROM crosstab( 'SELECT extract(year from dsf) AS Year, extract(month from dsf) AS Month, count(*) FROM sf GROUP BY Year, Month', 'SELECT * FROM my_month ) AS (Year int, jan int, feb int, mar int, apr int, may int, jun int, jul int, aug int, sep int, oct int, nov int, dec int ) ORDER BY Year;

13 Значения Идентификатор категории определяет содержимое столбцов Идентификатор строк определяет содержимое строк, таких столбцов м.б. >=1 Результат: