CREATE VIEW

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

Синтаксис

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW имя [ ( имя_столбца [, ...] ) ]
    [ WITH ( имя_параметра_представления [= значение_параметра_представления] [, ... ] ) ]
    AS запрос
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Описание

Команда CREATE VIEW определяет представление запроса. Так как это представление физически не материализуется, то указанный запрос будет выполняться при каждом обращении к представлению в нем.

Команда CREATE OR REPLACE VIEW действует схожим образом, но если представление с тем же именем уже существует, оно заменяется. Новый запрос должен генерировать те же столбцы, что и существующий запрос представления (то есть те же имена столбцов в том же порядке и с теми же типами данных), но он может добавлять дополнительные столбцы в конец этого списка. Вычисления, в результате которых формируются выходные столбцы, могут быть совершенно другими.

Если задано имя схемы (например, CREATE VIEW myschema.myview ...), то представление создается в указанной схеме. В остальных случаях оно создается в текущей схеме. Временные представления существуют в специальной схеме, поэтому при создании таких представлений имя схемы задать нельзя. Имя представления должно отличаться от имени любого другого отношения (таблицы, последовательности, индекса, представления, материализованного представления или сторонней таблицы) в той же схеме.

Параметры

TEMPORARY или TEMP

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

RECURSIVE

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

CREATE RECURSIVE VIEW [ схема . ] имя_представления (имена_столбцов) AS SELECT ...;

равнозначен

CREATE VIEW [ схема . ] имя_представления AS WITH RECURSIVE имя_представления (имена_столбцов) AS (SELECT ...) SELECT имена_столбцов FROM имя_представления;

Для рекурсивного представления обязательно должен задаваться список с именами столбцов.

имя

Имя создаваемого представления (может быть дополнено схемой).

имя_столбца

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

WITH ( имя_параметра_представления [= значение_параметра_представления] [, ... ] )

В этом предложении указываются необязательные параметры для представления. Поддерживаются следующие параметры:

  • check_option (enum)
    Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже).
  • security_barrier (boolean)
    Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. Дополнительную информацию см. в разделе Политики защиты строк.
  • security_invoker (boolean)
    С этим параметром для нижележащих базовых отношений будут проверяться права пользователя представления, а не его владельца. Подробную информацию см. в примечаниях ниже. В существующих представлениях все эти параметры можно изменить с помощью команды ALTER VIEW.

запрос

Команда SELECT или VALUES, которая предоставляет столбцы и строки этого представления.

WITH [ CASCADED | LOCAL ] CHECK OPTION

Этот параметр управляет поведением автоматически изменяемых представлений. Если этот параметр задан, то команды INSERT и UPDATE в представлении будут проверяться на предмет того, удовлетворяют ли новые строки условию, определяющему представление (то есть будет выполнена проверка новых строк на видимость через это представление). Если строки не видны, изменение будет отклонено. Если параметр CHECK OPTION не указан, то командам INSERT и UPDATE в представлении разрешается создавать строки, которые не будут видны через это представление. Поддерживаются следующие варианты проверки:

  • LOCAL
    Новые строки проверяются только на соответствие условиям, определенным непосредственно в самом представлении. Любые условия, определенные в нижележащих базовых представлениях, не проверяются (за исключением случаев, когда в них тоже есть указание CHECK OPTION).
  • CASCADED
    Новые строки проверяются на соответствие условиям этого представления и всех нижележащих базовых представлений. Если указано CHECK OPTION, а LOCAL и CASCADED опущены, то предполагается значение CASCADED. Не допускается использование CHECK OPTION с рекурсивными (RECURSIVE) представлениями.
    Обратите внимание, что CHECK OPTION поддерживается только для автоматически изменяемых представлений, не имеющих триггеров INSTEAD OF или правил INSTEAD. Если автоматически изменяемое представление определено поверх базового представления с триггерами INSTEAD OF, то параметр LOCAL CHECK OPTION можно использовать для проверки ограничений автоматически изменяемого представления, но условия базового представления с триггерами INSTEAD OF проверяться не будут (каскадный вариант проверки не будет спускаться к представлению, изменяемому триггером, и любые варианты проверки, определенные напрямую для такого представления, будут игнорироваться). Если для представления или любого из его базовых отношений определено правило INSTEAD, которое приводит к перезаписи команды INSERT или UPDATE, то все варианты проверки в перезаписанном запросе будут игнорироваться, в том числе любые проверки из автоматически изменяемых представлений, определенных поверх отношения с правилом INSTEAD.

Примечания

Для удаления представлений воспользуйтесь командой DROP VIEW.

Проследите за тем, чтобы столбцам представления были присвоены желаемые имена и типы. Например, такая команда:

CREATE VIEW vista AS SELECT 'Hello World';

плоха тем, что по умолчанию именем столбца будет ?column?, а типом данных — text, а это может быть совсем не тем, что вы хотели. Лучше записывать строковую константу в результате представления примерно так:

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

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

Если свойство представления security_invoker установлено в true, доступ к нижележащим базовым отношениям определяется правами пользователя, выполняющего запрос, а не владельца представления. Таким образом, пользователь представления с характеристикой безопасности вызывающего, должен иметь соответствующие права для этого представления и его нижележащих базовых отношений.

Если любое из нижележащих базовых отношений является представлением с характеристикой безопасности вызывающего, оно будет обрабатываться так, как если бы к нему обратились напрямую из исходного запроса. Таким образом, представление с характеристикой безопасности вызывающего всегда будет проверять свои нижележащие базовые отношения, используя права текущего пользователя, даже если к нему обращаются из представления без свойства security_invoker.

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

Функции, вызываемые в представлении, обрабатываются так, будто они вызывались напрямую из запроса, в котором фигурирует это представление. Поэтому пользователь представления должен иметь права на вызов всех функций, задействованных в этом представлении. Функции в представлении выполняются с правами пользователя, выполняющего запрос или владельца функции, в зависимости от того, определены ли функции как SECURITY INVOKER или как SECURITY DEFINER. Поэтому, к примеру, при вызове функции CURRENT_USER напрямую из представления всегда будет возвращаться имя вызывающего пользователя, а не владельца представления. Свойство представления security_invoker на это не влияет, так что представление с security_invoker, установленным в false, не равнозначно функции SECURITY DEFINER, и эти концепции не следует путать.

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

При выполнении CREATE OR REPLACE VIEW для существующего представления изменяется только правило SELECT, определяющее представление, а также все параметры WITH ( ... ) и его CHECK OPTION. Другие свойства представления, включая владельца, права и правила, отличные от SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).

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

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

  • Список FROM в запросе, определяющем представление, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.

  • Определение представления не должно содержать предложений WITH, DISTINCT, GROUP BY, HAVING, LIMIT и OFFSET BY на верхнем уровне запроса.

  • Определение представления не должно содержать операций с множествами (UNION, INTERSECT или EXCEPT) на верхнем уровне запроса.

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

Автоматически изменяемое представление может одновременно содержать изменяемые и неизменяемые столбцы. Столбец является изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать в него значение, то возникнет ошибка.

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

Если автоматически изменяемое представление содержит условие WHERE, то это условие ограничивает набор строк базового отношения, которые в этом представлении доступны для модификаций командами UPDATE и DELETE . Однако UPDATE может изменить строку так, что она перестанет соответствовать условию WHERE и, как следствие, больше не будет видна через представление. Аналогично команда INSERT может добавить в базовое отношение строки, которые не удовлетворяют условию WHERE и поэтому не будут видны через представление (ON CONFLICT UPDATE может подобным образом воздействовать на существующую строку, не видимую через представление). Чтобы предотвратить создание подобных невидимых строк командами INSERT и UPDATE, можно воспользоваться указанием CHECK OPTION.

Если автоматически изменяемое представление помечено как имеющее свойство security_barrier, то все условия WHERE этого представления (и все условия с герметичными операторами (помеченными как LEAKPROOF)) будут всегда вычисляться перед любыми условиями, добавленными пользователем представления. Подробную информацию см. в разделе Правила и права. Обратите внимание, что по этой причине строки, которые в итоге не возвращаются (потому что не прошли проверку в пользовательском условии WHERE), могут все равно остаться заблокированными. Чтобы определить, какие условия применяются на уровне отношения (и, как следствие, не блокируют строки), а какие нет, можно воспользоваться командой EXPLAIN.

Более сложные представления, не удовлетворяющие этим условиям, по умолчанию доступны только для чтения: система не позволит выполнить операции добавления, изменения или удаления строк в таком представлении. Получить для них эффект изменяемого представления можно, создав триггеры INSTEAD OF, которые будут преобразовывать попытки модифицировать представление в соответствующие действия с другими таблицами. Подробную информацию см. на справочной странице команды CREATE TRIGGER. Также есть возможность создавать правила (см. справочную страницу команды CREATE RULE), но на практике триггеры проще для понимания и корректного применения.

Учтите, что пользователь, выполняющий в представлении операции добавления, изменения или удаления, должен иметь соответствующие права для этого представления. Кроме того, по умолчанию владелец представления должен иметь соответствующие права в нижележащих базовых отношениях, хотя пользователь, выполняющий эти операции, в таких правах не нуждается (см. раздел Правила и права). Однако если свойство представления security_invoker установлено в true, соответствующие права для нижележащих базовых отношений должен иметь как раз пользователь, осуществляющий модификации, а не владелец представления.

Примеры

Создание представления, содержащего все комедийные фильмы:

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

Эта команда создаст представление со столбцами, которые имеются в таблице film в момент выполнения команды. Хотя при создании представления было указано *, столбцы, добавляемые в таблицу позже, не будут являться частью представления.

Создание представления с указанием LOCAL CHECK OPTION:

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

Эта команда создаст представление на базе представления comedies, выводящее только комедии (kind = 'Comedy') универсальной возрастной категории (classification = 'U'). Любая попытка выполнить в представлении INSERT или UPDATE со строкой, не удовлетворяющей условию classification = 'U', будет отвергнута, но ограничение по полю kind (тип фильма) проверяться не будет.

Создание представления с указанием CASCADED CHECK OPTION:

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

Это представление будет проверять, удовлетворяют ли новые строки обоим условиям: по столбцу kind и по столбцу classification.

Создание представления с изменяемыми и неизменяемыми столбцами:

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';

Это представление будет поддерживать операции INSERT, UPDATE и DELETE. Изменяемыми будут все столбцы из таблицы films, тогда как вычисляемые столбцы country и avg_rating будут доступны только для чтения.

Создание рекурсивного представления, состоящего из чисел от 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;

Обратите внимание, что несмотря на то, что имя рекурсивного представления в этой команде CREATE дополнено схемой, внутренняя ссылка представления на себя же схемой не дополняется. Это связано с тем, что имя неявно создаваемого общего табличного выражения (Common Table Expression, CTE) не может дополняться схемой.

Совместимость

Команда CREATE OR REPLACE VIEW, как и концепция временного представления, является языковым расширением QHB. Также расширениями являются предложение WITH ( ... ) и представления с барьером безопасности и характеристикой безопасности вызывающего.

См. также

ALTER VIEW, DROP VIEW, CREATE MATERIALIZED VIEW

Copyright © KVANTOM LLC, 2019-2025