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-запросы, не использующие представление

Любой добавляемый в представлении столбец должен присутствовать в базовой таблице. Изменим таблицу 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] Для представления используется только временная таблица | Данные для представления сначала помещаются во временную таблицу, а затем выполняется инструкция (удобно для краткого снятия блокировок) | Никакое предложение по алгоритму не присутствует

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

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

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

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

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

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

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

Результаты:

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;

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