CREATE VIEW#

CREATE VIEW
CREATE VIEW
Назад НаверхSQL КомандыНачало Далее

CREATE VIEW

CREATE VIEW — определить новый вид

Синтаксис

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

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

Параметры

TEMPORARY or TEMP

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

Если любая из таблиц, на которые ссылается представление, является временной, представление создается как временное представление (независимо от того, указано ли TEMPORARY или нет).

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;

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

name

Имя (опционально с указанием схемы) представления, которое будет создано.

column_name

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

WITH ( view_option_name [= view_option_value] [, ... ] )

Это условие определяет необязательные параметры для представления; поддерживаются следующие параметры:

check_option (enum)

Этот параметр может быть либо local, либо cascaded, и эквивалентен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже).

security_barrier (boolean)

Следует использовать это, если представление предназначено для обеспечения безопасности на уровне строк. См. Раздел 39.5 для получения полной информации.

security_invoker (boolean)

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

Все вышеуказанные параметры могут быть изменены на существующих представлениях с помощью ALTER VIEW.

query

Команда 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;

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

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

Если одна из базовых отношений является представлением с вызовом безопасности, оно будет обрабатываться так, как если бы оно было доступно непосредственно из исходного запроса. Таким образом, представление с вызовом безопасности всегда будет проверять свои базовые отношения с использованием разрешений текущего пользователя, даже если оно доступно из представления без свойства 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 выражений на верхнем уровне.

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

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

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

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

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

Если автоматически обновляемое представление помечено свойством security_barrier, то все условия WHERE представления (а также любые условия, использующие операторы, помеченные как LEAKPROOF) всегда будут вычисляться перед любыми условиями, добавленными пользователем представления. См. полные сведения в разделе Раздел 39.5. Обратите внимание, что из-за этого строки, которые в конечном итоге не возвращаются (потому что они не проходят условия WHERE пользователя), могут все равно быть заблокированы. Команда EXPLAIN может использоваться для просмотра условий, применяемых на уровне отношения (и, следовательно, не блокирующих строки), и которые не применяются.

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

Обратите внимание, что пользователь, выполняющий вставку, обновление или удаление в представлении, должен иметь соответствующие привилегии на вставку, обновление или удаление в представлении. Кроме того, по умолчанию владелец представления должен иметь соответствующие привилегии на базовые отношения, в то время как пользователь, выполняющий обновление, не требует никаких разрешений на базовые отношения (см. Раздел 39.5). Однако, если у представления установлено значение 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, его внутренняя ссылка на себя не имеет указания схемы. Это происходит потому, что имя неявно созданного CTE не может быть указано с указанием схемы.

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

CREATE OR REPLACE VIEW - это расширение языка Tantor SE. Также является расширением концепция временного представления. Предложение WITH ( ... ) также является расширением, а также представления с барьером безопасности и представления с вызывающими правами безопасности.

Назад Наверх Далее
CREATE USER MAPPING Начало DEALLOCATE