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)

Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. За дополнительными подробностями обратитесь к Разделу 39.5.

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 нельзя использовать с рекурсивными представлениями.

Заметьте, что 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 имеет значение 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 может подобным образом воздействовать на существующую строку, не видимую через представление). Чтобы запретить командам INSERT и UPDATE создавать такие строки, которые не видны через представление, можно воспользоваться указанием CHECK OPTION.

Если автоматически изменяемое представление имеет свойство 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 — языковое расширение Postgres Pro. Так же расширениями являются концепция временного представления, предложение WITH ( ... ), представления с барьером безопасности и представления с контекстом безопасности вызывающего.