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 (см. ниже). На существующих представлениях этот параметр можно изменить с помощью команды ALTER VIEW.

security_barrier (boolean)

Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. Дополнительную информацию см. в разделе Политики безопасности строк.

запрос

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

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

При выполнении CREATE OR REPLACE VIEW для существующего представления изменяется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила (кроме 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)) будут всегда вычисляться перед условиями, добавленными пользователем представления. Подробную информацию см. в разделе Система правил QHB. Обратите внимание, что по этой причине строки, которые в итоге не были возвращены (потому что не прошли проверку в пользовательском условии WHERE), могут всё же остаться заблокированными. Чтобы определить, какие условия применяются на уровне отношения (и, как следствие, избавляют часть строк от блокировки), можно воспользоваться командой EXPLAIN.

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

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

Примеры

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

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

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

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

См. также

ALTER VIEW, DROP VIEW, CREATE MATERIALIZED VIEW