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;

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

При выполнении 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)) будут всегда вычисляться перед любыми условиями, добавленными пользователем представления.

Обратите

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

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

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

Примеры

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

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

Copyright © KVANTOM LLC, 2019-2023