postgres view что и зачем

Как использовать материализованное представление PostgreSQL

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

Зачем использовать материализованные представления?

Если команда просмотра слишком медленная для вас, вы можете предпочесть материализованное представление. Материализованные представления обладают большой универсальностью, позволяя сохранять материальное представление в базе данных с более коротким временем доступа. Предположим, что вам необходимо создать запрос к базе данных для объединения нескольких таблиц, удаления строк из объединенной коллекции и сортировки таблиц различными способами. Это может быть сложный и трудоемкий запрос, и без материализованных представлений вы в конечном итоге будете использовать материализованное представление для решения этой дилеммы. В этой статье рассказывается, как использовать материализованные представления в PostgreSQL.

Синтаксис

>> CREATE MATERIALIZED VIEW view_name AS query WITH [ NO ] DATA ;

Объяснение этого общего взгляда следующее:

Как использовать материализованные представления

Запустите оболочку командной строки PostgreSQL, чтобы начать работу с материализованными представлениями.

Укажите имя сервера, базу данных, с которой вы хотите работать, номер порта и имя пользователя, чтобы начать использовать командную оболочку. Оставьте эти поля пустыми, если вы хотите использовать систему по умолчанию.

Пример 1: простой вид

Чтобы понять материализованное представление, вам сначала нужно понять простые представления. Итак, создайте новую таблицу «Студент», используя команду CREATE TABLE, как добавлено.

После этого вставьте в него данные с помощью запроса INSERT.

Получите записи таблицы «Студент» с помощью оператора SELECT для простого представления.

Пример 2: Простое материализованное представление

Теперь пора охватить материализованное представление. Мы будем использовать таблицу «Студент» для создания материализованного представления. Мы создадим материализованное представление с именем ’std_view’ с помощью команды ’CREATE MATERIALIZED VIEW’. В этом представлении мы извлечем поле имени студента «sname» из таблицы «Student», сгруппируем и отсортируем его в возрастающем порядке в столбце «sname».

>> CREATE MATERIALIZED VIEW std_view AS SELECT sname FROM Student GROUP BY sname ORDER BY sname ;

Теперь, используя запрос SELECT для выполнения представления, мы вернем имена студентов в столбце sname таблицы Student.

Пример 3: Материализованное представление с использованием предложения WHERE

Теперь мы создадим материализованное представление, используя предложение WHERE. Рассмотрим следующую таблицу «Студент» с некоторыми изменениями ее значений.

Затем мы создадим материализованное представление с именем teststd, используя запрос CREATE MATERIALIZED VIEW. Мы выберем записи таблицы «Студент», где значение столбца «возраст» больше «25», используя предложение WHERE. Запрос работает правильно, как видно на картинке.

Наконец, мы выполним только что созданное материализованное представление с помощью команды SELECT, как показано ниже. Вы увидите, что он вернет все записи из таблицы «Студент», в которой столбец «возраст» имеет значение больше «25».

Пример 4: Обновить материализованное представление с помощью предложения WITH NO DATA

В этом примере мы создадим материализованное представление, в котором мы будем использовать предложение WITH NO DATA для обновления представления. Предположим, что следующая таблица «Студент» с некоторыми изменениями в ее значениях.

Теперь мы создадим материализованное представление teststd. Это представление выберет записи из таблицы «студент», в которой возраст учащихся меньше «40». Выбранные записи будут сгруппированы и отсортированы по возрастанию в столбце sid. В конце запроса мы будем использовать предложение WITH NO DATA, чтобы указать, что запрос не будет сохранять какую-либо информацию в материализованном представлении. Представление, показанное ниже, должно успешно выполнить эти действия.

Когда вы добавляете предложение «WITH NO DATA» к материализованному представлению, оно создает пустое представление. Это материализованное представление не запрашивается. Как вы можете видеть на следующем изображении, он не получает записи во вновь созданном представлении.

Оператор REFRESH MATERIALIZED VIEW используется для импорта данных в материализованное представление. Заполните материализованное представление, выполнив следующий запрос REFRESH MATERIALIZED VIEW в оболочке. Как видите, этот запрос сработал эффективно.

>> REFRESH MATERIALIZED VIEW teststd ;

Опять же, выберите записи материализованного представления teststd с помощью оператора SELECT в оболочке. На этот раз запрос SELECT работает правильно, потому что оператор REFRESH загрузил содержимое в материализованное представление.

Пример 5: Отбросить материализованное представление

Следующая команда удалит материализованное представление.

Заключение

В этой статье показано, как использовать материализованные представления с помощью предложения WHERE и запросов REFRESH в оболочке командной строки. строки.

Источник

38.2. Система правил и представления

Представления в PostgreSQL реализованы на основе системы правил. Фактически по сути нет никакого отличия

от следующих двух команд:

так как именно эти действия CREATE VIEW выполняет внутри. Это имеет некоторые побочные эффекты. В частности, информация о представлениях в системных каталогах PostgreSQL ничем не отличается от информации о таблицах. Поэтому при анализе запроса нет абсолютно никакой разницы между таблицами и представлениями. Они представляют собой одно и то же — отношения.

38.2.1. Как работают правила SELECT

Правила ON SELECT применяются ко всем запросам на последнем этапе, даже если это команда INSERT, UPDATE или DELETE. Эти правила отличаются от правил других видов тем, что они модифицируют непосредственно дерево запросов, а не создают новое. Поэтому мы начнём описание с правил SELECT.

В настоящее время возможно только одно действие в правиле ON SELECT и это должно быть безусловное действие SELECT, выполняемое в режиме INSTEAD. Это ограничение было введено, чтобы сделать правила достаточно безопасными для применения обычными пользователями, так что действие правил ON SELECT сводится к реализации представлений.

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

Например, нам нужна простейшая функция min, которая возвратит минимальное из двух целых чисел. Её можно создать так:

Таблицы, которые понадобятся нам для описания системы правил, выглядят так:

Как можно догадаться, в них хранятся данные обувной фабрики.

Представления создаются так:

Команда CREATE VIEW для представления shoelace (самого простого из имеющихся) создаёт отношение shoelace и запись в pg_rewrite о правиле перезаписи, которое должно применяться, когда в запросе на выборку задействуется отношение shoelace. Для этого правила не задаются условия применения (о них рассказывается ниже, в описании правил не для SELECT, так как правила SELECT в настоящее бывают только безусловными) и оно действует в режиме INSTEAD. Заметьте, что условия применения отличаются от условий фильтра запроса, например, действие для нашего правила содержит условие фильтра. Действие правила выражается одним деревом запроса, которое является копией оператора SELECT в команде, создающей представление.

Замечание: Два дополнительных элемента списка отношений NEW и OLD, которые можно увидеть в соответствующей строке pg_rewrite, не представляют интереса для правил SELECT.

Сейчас мы наполним таблицы unit (единицы измерения), shoe_data (данные о туфлях) и shoelace_data (данные о шнурках) и выполним простой запрос к представлению:

Это самый простой запрос SELECT, который можно выполнить с нашими представлениями, и мы воспользуемся этим, чтобы объяснить азы правил представлений. Запрос SELECT * FROM shoelace интерпретируется анализатором запросов и преобразуется в дерево запроса:

Это дерево передаётся в систему правил, которая проходит по списку отношений и проверяет, есть ли какие-либо правила для этих отношений. Обрабатывая элемент отношения shoelace (сейчас он единственный), система правил находит правило _RETURN с деревом запроса:

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

Однако есть одно различие: в списке отношений подзапроса будут содержаться два дополнительных элемента: shoelace old и shoelace new. Эти элементы не принимают непосредственного участия в запросе, так как они не задействованы в дереве соединения подзапроса и в целевом списке. Механизм перезаписи использует их для хранения информации о проверке прав доступа, которая изначально хранилась в элементе, указывающем на представление. Таким образом, исполнитель будет по-прежнему проверять, имеет ли пользователь необходимые права для доступа к представлению, хотя в перезаписанном запросе это представление не фигурирует непосредственно.

Так было применено первое правило. Система правил продолжит проверку оставшихся элементов списка отношений на верхнем уровне запроса (в данном случае таких элементов нет) и рекурсивно проверит элементы списка отношений в добавленном подзапросе, не ссылаются ли они на представления. (Но old и new разворачиваться не будут — иначе мы получили бы бесконечную рекурсию!) В этом примере для shoelace_data и unit нет правил перезаписи, так что перезапись завершается и результат, полученный выше, передаётся планировщику.

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

На этот раз анализатор запроса выводит такое дерево:

Первое правило применяется к представлению shoe_ready и в результате получается дерево запроса:

Подобным образом, правила для shoe и shoelace подставляются в список отношений, что даёт окончательное дерево запроса:

На практике планировщик будет сворачивать это дерево до двух уровней: команды нижнего уровня SELECT будут «подняты» к среднему SELECT, так как обрабатывать их отдельно нет необходимости. Но средний оператор SELECT не будет совмещён с верхним, так как он содержит агрегатные функции. Если поднять его выше, поведение самого верхнего SELECT изменится нежелательным образом. В целом же, сворачивание дерева запросов — это оптимизация, которая не должна затрагивать работу механизма перезаписи.

38.2.2. Правила представлений не для SELECT

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

Есть только несколько отличий между деревом запроса для SELECT и деревом для другой команды. Очевидно, у них различные типы команд, и для команды, отличной от SELECT, результирующее отношение указывает на элемент в списке отношений, куда должен попасть результат. Все остальные компоненты в точности те же. Поэтому, например, если взять таблицы t1 и t2 с колонками a и b, деревья запросов для этих операторов:

будут практически одинаковыми. В частности:

Списки отношений содержат элементы для таблиц t1 и t2.

Выходные списки содержат одну переменную, указывающую на колонку b элемента-отношения для таблицы t2.

Выражения условий сравнивают колонки a обоих элементов-отношений на равенство.

Деревья соединений показывают простое соединение между t1 и t2.

Как следствие, для обоих деревьев строятся похожие планы выполнения, с соединением двух таблиц. Для UPDATE планировщик добавляет в выходной список недостающие колонки из t1 и окончательное дерево становится таким:

В результате исполнитель, обрабатывающий соединение, выдаёт тот же результат, что и запрос:

Но с UPDATE есть маленькая проблема: часть плана исполнителя, в которой выполняется соединение, не представляет, для чего предназначены результаты соединения. Она просто выдаёт результирующий набор строк. Фактически есть одна команда SELECT, а другая, UPDATE, обрабатывается исполнителем выше, где он уже знает, что это команда UPDATE и что результат должен попасть в таблицу t1. Но какие из строк таблицы должны заменяться новыми?

Зная всё это, мы можем применять правила представлений абсолютно таким же образом к любой команде — никаких различий нет.

38.2.3. Преимущества представлений в PostgreSQL

Выше было показано, как система правил внедряет определения представлений в исходное дерево запроса. Во втором примере простой запрос SELECT к одному представлению создал окончательное дерево запроса, соединяющее 4 таблицы (таблица unit использовалась дважды с разными именами).

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

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

Но что произойдёт, если записать имя представления в качестве целевого отношения команды INSERT, UPDATE или DELETE? Если проделать подстановки, описанные выше, будет получено дерево запроса, в котором результирующее отношение указывает на элемент-подзапрос, что не будет работать. Однако PostgreSQL даёт ряд возможностей, чтобы сделать представления изменяемыми.

Если подзапрос выбирает данные из одного базового отношения и он достаточно прост, механизм перезаписи может автоматически заменить его нижележащим базовым отношением, чтобы команды INSERT, UPDATE или DELETE обращались к базовому отношению. Представления, «достаточно простые» для этого, называются автоматически изменяемыми. Подробнее виды представлений, которые могут изменяться автоматически, описаны в CREATE VIEW.

Эту задачу также можно решить, создав триггер INSTEAD OF для представления. В этом случае перезапись будет работать немного по-другому. Для INSERT механизм перезаписи не делает с представлением ничего, оставляя его результирующим отношением запроса. Для UPDATE и DELETE ему по-прежнему придётся разворачивать запрос представления, чтобы получить «старые» строки, которые эта команда попытается изменить или удалить. Поэтому представление разворачивается как обычно, но в запрос добавляется ещё один элемент списка отношений, указывающий на представление в роли результирующего отношения.

Кроме того, пользователь может определить правила INSTEAD, в которых задать действия замены для команд INSERT, UPDATE и DELETE с представлением. Эти правила обычно преобразуют команду в другую команду, изменяющую одну или несколько таблиц, а не представление. Это тема следующего раздела.

Заметьте, что такие правила вычисляются сначала, перезаписывая исходный запрос до того, как он будет планироваться и выполняться. Поэтому, если для представления определены и триггеры INSTEAD OF, и правила для INSERT, UPDATE или DELETE, сначала вычисляются правила, а в зависимости от их действия, триггеры могут не вызываться вовсе.

Автоматическая перезапись запросов INSERT, UPDATE или DELETE с простыми представлениями всегда производится в последнюю очередь. Таким образом, если у представления есть правила или триггеры, они переопределяют поведение автоматически изменяемых представлений.

Если для представления не определены правила INSTEAD или триггеры INSTEAD OF, и запрос не удаётся автоматически переписать в виде обращения к нижележащему базовому отношению, возникает ошибка, потому что исполнитель не сможет изменить такое представление.

Источник

PostgreSQL Views

Вступление

Версия: PostgreSQL 9.3.5

Содержание:

Как создать PostgreSQL View?

CREATE VIEW определяет представление запроса. Представление не материализовано физически. Вместо этого запрос выполняется каждый раз, когда на представление ссылаются в запросе. По умолчанию представление связано с базой данных по умолчанию (используемой в настоящее время базой данных). Чтобы связать представление с заданной базой данных, укажите имя как имя_базы_данных . view_name при его создании. Вот полный синтаксис:

Синтаксис:

Замечания:

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

Примеры

Вот пример использования RECURSIVE:

Пример таблицы: сотрудники

Пример таблицы: расположение

Пример таблицы: отделы

PostgreSQL СОЗДАТЬ ВИД С ГДЕ

Команда CREATE VIEW может использоваться с предложением WHERE.

Пример:

Приведенный выше оператор PostgreSQL создаст представление emp_view, содержащее записи (для столбцов employee_id, first_name, last_name и hire_date) таблицы сотрудников, если эти записи содержат значение 200 для столбца Department_id.

PostgreSQL CREATE VIEW с помощью AND и OR

Команда CREATE VIEW может использоваться с операторами AND и OR.

Пример:

PostgreSQL СОЗДАТЬ ВИД с GROUP BY

Команда CREATE VIEW может использоваться с предложением GROUP BY.

Пример:

Приведенный выше оператор создаст представление «my_view», в котором будут собраны все записи, сгруппированные по департаменту_id, а также сохранен идентификатор департамента и число сотрудников для каждого отдела (отдел_идентификатора) из таблицы сотрудников.

PostgreSQL СОЗДАТЬ ВИД с ORDER BY

Команда CREATE VIEW может использоваться с предложением ORDER BY.

Пример:

Приведенный выше оператор PostgreSQL создаст представление «my_view», в котором будут собраны все записи, сгруппированные по Department_id и отсортированные по Department_id и количеству сотрудников для каждого отдела (отдел_ид) из таблицы сотрудников.

PostgreSQL СОЗДАТЬ ПРОСМОТР МЕЖДУ И В

Команда CREATE VIEW может использоваться с оператором BETWEEN и IN.

Пример:

PostgreSQL СОЗДАТЬ ВИД с LIKE

Команда CREATE VIEW может использоваться с оператором LIKE.

Пример:

Вышеприведенный оператор PostgreSQL создаст представление «my_view», принимающее все записи таблицы сотрудников, если (A) first_name сотрудника не начинается с «T» и (B) last_name сотрудника не начинается с «T».

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

Команда CREATE VIEW может использоваться с подзапросами.

Пример:

Приведенный выше оператор PostgreSQL создаст представление «my_view», в котором будут храниться все записи таблицы employee_id, first_name, last_name of employee, если Department_id удовлетворяет условию, определенному в подзапросе (за которым следует Department_id IN).

PostgreSQL СОЗДАТЬ ВИД с JOIN

Команда CREATE VIEW может использоваться вместе с оператором JOIN.

Пример:

Приведенный выше оператор PostgreSQL создаст представление my_view вместе с оператором JOIN.

Здесь оператор JOIN извлекает employee_id, first_name, last_name из таблицы сотрудников, а Department_id и location_id из таблицы расположений, если отдел_ид таблицы сотрудников и местоположение находятся на одном уровне.

PostgreSQL СОЗДАТЬ ВИД с UNION

Команда CREATE VIEW может использоваться с UNION.

Пример:

Приведенный выше оператор PostgreSQL создаст представление «my_view», содержащее столбцы, как в «employee».

Записи будут вставлены с объединением трех подзапросов.

Первый запрос вставляет эти строки в представление «my_view» из таблицы «employee», у которого «manager_id» равен «100».

Второй запрос вставляет эти строки в представление «my_view» из таблицы «employee», строки которой имеют столбец «first_name», начинающийся с любой буквы от «P» до «W».

Третий запрос вставляет эти строки в представление «my_view» из таблицы «employee», строки которой имеют любое из следующих значений 7000,9000,10000,12000 в «salary».

Оператор ALTER VIEW изменяет определение существующего представления. Для этого оператора требуются привилегии CREATE VIEW и DROP для представления, а также некоторые привилегии для каждого столбца, указанного в операторе SELECT. Синтаксис оператора похож на CREATE VIEW. Вот синтаксис:

Параметры:

Пример:

Переименовать представление abc в xyz

Чтобы прикрепить значение столбца по умолчанию к обновляемому представлению:

Оператор DROP VIEW используется для удаления представлений. Чтобы удалить представление, вы должны иметь привилегию DROP для каждого представления. Вот синтаксис:

Синтаксис

Параметры:

Пример:

Эта команда удалит представление под названием «test_view»:

Предыдущая: ПОДПИСКИ
Далее: ТРИГГЕРС

Источник