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

Евгения Кузина

Представления

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

Представления в PostgresSQL реализованы на основе системы правил. По сути, нет никакой разницы между одним оператором и набором команд, приведенными ниже.

CREATE VIEW view1 AS SELECT * FROM table1;
CREATE TABLE view1 (<the same column list as table1>);
CREATE RULE "_RETURN" AS ON SELECT TO view1 DO INSTEAD
    SELECT * FROM table1;

Второй фрагмент кода представляет собой внутренний код команды CREATE VIEW. Информация о представлении в системных каталогах PostgreSQL такая же, как и о таблице. Для парсера нет разницы между таблицей и представлением, для него это одно и то же — отношения. Преимущество реализации представлений через систему правил заключается в том, что планировщик получает в одном дереве запроса всю информацию о таблицах, которые нужно прочитать, о том, как связаны эти таблицы, об условиях в представлениях, а также об условиях, заданных в исходном запросе. Планировщик должен решить, какой способ является лучшим для выполнения запроса, и чем больше информации он имеет, тем лучшим может быть это решение. За подробностями обратитесь к статье Views and the Rule System.

Создание представления

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

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

TEMPORARY или TEMP

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

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

RECURSIVE

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

имя

Имя создаваемого представления (можно указать схему)

имя_столбца

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

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

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

  • check_option (enum) — может быть local или cascaded. Это эквивалент выражения WITH [ CASCADED | LOCAL ] CHECK OPTION, описанного ниже. Эту опцию можно изменить в существующих представлениях с помощью команды ALTER VIEW.

  • security_barrier (boolean) — используется, если представление обеспечивает безопасность на уровне строк. Для получения дополнительной информации обратитесь к статье Rules and Privileges.

запрос

Команда SELECT или VALUES, возвращающая столбцы и строки представления

WITH [CASCADED | LOCAL] CHECK OPTION

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

  • LOCAL — новые строки проверяются только на соответствие условиям, определенным непосредственно в самом представлении. Любые условия, определенные в базовых представлениях, не проверяются (если в них также не указана CHECK OPTION).

  • CASCADED — новые строки проверяются на соответствие условиям представления и всех базовых представлений.

Если указана CHECK OPTION без LOCAL или CASCADED, используется CASCADED.

CHECK OPTION поддерживается только в представлениях, которые автоматически обновляются и не имеют триггеров INSTEAD OF или правил INSTEAD. Если обновляемое представление определено на базе представления, которое имеет триггеры INSTEAD OF, LOCAL CHECK OPTION можно использовать для проверки условий в обновляемом представлении, но условия в базовом представлении с INSTEAD OF триггерами не проверяются. Если представление или какое-либо из его базовых отношений содержит правило INSTEAD, которое вызывает перезапись команды INSERT или UPDATE, все параметры проверки будут игнорироваться в перезаписанном запросе

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

Если указано имя схемы, например, CREATE VIEW schema1.view1 …​, PostgreSQL создает представление в указанной схеме, в противном случае — в текущей. Временные представления существуют в специальной схеме, поэтому имя схемы нельзя указывать при создании временных представлений. Имя представления должно отличаться от имен других представлений, таблиц, последовательностей, индексов или сторонних таблиц в схеме.

Например, имеется таблица books:

 book_id |                title                | author_id | public_year |  genre  | evaluation | price
---------+-------------------------------------+-----------+-------------+---------+------------+-------
       1 | Mrs. Dalloway                       |         1 |        1925 | novel   |       7.82 |   360
       2 | To the Lighthouse                   |         1 |        1927 | novel   |       8.45 |   440
       3 | To Kill a Mockingbird               |         2 |        1960 | novel   |       7.48 |   750
       4 | The Great Gatsby                    |         3 |        1925 | novel   |       9.23 |   900
       5 | The Lord of the Rings               |         4 |        1955 | fantasy |       9.49 |  1200
       6 | 1984                                |         5 |        1949 | sci-fi  |       8.17 |   520
       7 | The Hobbit, or There and Back Again |         4 |        1937 | fantasy |       9.32 |  1100
       8 | War and Peace                       |         6 |        1869 | novel   |       9.69 |  1500
       9 | Hyperion                            |         7 |        1989 | sci-fi  |       9.46 |   610
      10 | The Time Machine                    |         8 |        1895 | sci-fi  |       8.12 |   450

Создайте представление, содержащее все книги жанра novel.

CREATE VIEW novels AS
    SELECT *
    FROM books
    WHERE genre = 'novel';

Отобразите содержимое представления.

SELECT * FROM novels;

Результат:

 book_id |         title         | author_id | public_year | genre | evaluation | price
---------+-----------------------+-----------+-------------+-------+------------+-------
       1 | Mrs. Dalloway         |         1 |        1925 | novel |       7.82 |   360
       2 | To the Lighthouse     |         1 |        1927 | novel |       8.45 |   440
       3 | To Kill a Mockingbird |         2 |        1960 | novel |       7.48 |   750
       4 | The Great Gatsby      |         3 |        1925 | novel |       9.23 |   900
       8 | War and Peace         |         6 |        1869 | novel |       9.69 |  1500

Если имя и тип столбца не указан в представлении и не может быть взят из базового отношения, PostgreSQL присваивает имя ?column? и использует text в качестве типа по умолчанию. Например:

CREATE VIEW test AS SELECT 'Test';

Проверьте результат.

SELECT * from test;
 ?column?
----------
 Test

В таких случаях лучше явно указывать имя и тип столбца.

CREATE VIEW test1 AS SELECT text 'Test' AS column1;

Разрешения владельца представления определяют доступ к таблицам, которые используются в представлении. В некоторых случаях это можно использовать для обеспечения безопасного, но ограниченного доступа к базовым таблицам. См. Безопасность на уровне столбцов.

Обновляемые представления

Простые представления можно обновлять — PostgreSQL выполняет инструкции 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 и не видны в представлении. CHECK OPTION можно использовать, чтобы команды INSERT и UPDATE не создавали такие строки.

Создайте представление c LOCAL CHECK OPTION.

CREATE VIEW novels1 AS
    SELECT *
    FROM novels
    WHERE public_year < 1959
    WITH LOCAL CHECK OPTION;

Отобразите содержимое представления.

SELECT * FROM novels1;

Результат:

 book_id |       title       | author_id | public_year | genre | evaluation | price
---------+-------------------+-----------+-------------+-------+------------+-------
       1 | Mrs. Dalloway     |         1 |        1925 | novel |       7.82 |   360
       2 | To the Lighthouse |         1 |        1927 | novel |       8.45 |   440
       4 | The Great Gatsby  |         3 |        1925 | novel |       9.23 |   900
       8 | War and Peace     |         6 |        1869 | novel |       9.69 |  1500

Добавьте новую строку в novels1.

INSERT INTO novels1 (book_id, title, author_id, public_year, genre, evaluation, price)
VALUES (11, 'The World Set Free', 8, 1914, 'sci-fi', 7.9, 450);

Добавлена строка The World Set Free, но она не отображается в представлении novels1, поскольку строка не соответствует условию представления novels genre = 'novel'. Чтобы увидеть эту строку, выполните SELECT для таблицы books.

Создайте еще одно представление с параметром CASCADED CHECK OPTION и попробуйте добавить строку, которая не соответствует условию представления novels.

CREATE VIEW novels2 AS
    SELECT *
    FROM novels
    WHERE public_year < 1959
    WITH CASCADED CHECK OPTION;

INSERT INTO novels2 (book_id, title, author_id, public_year, genre, evaluation, price)
VALUES (12, 'The Food of the Gods', 8, 1904, 'sci-fi', 8.4, 620);

Произойдет ошибка:

ERROR:  new row violates check option for view "novels"
DETAIL:  Failing row contains (12, The Food of the Gods, 8, 1904, sci-fi, 8.4, 620).

Опция CASCADED CHECK OPTION проверяет условия текущего представления и всех базовых представлений.

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

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

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

Команда ALTER VIEW имеет следующий синтаксис:

ALTER VIEW [ IF EXISTS ] <имя> ALTER [ COLUMN ] <имя_столбца> SET DEFAULT <выражение>
ALTER VIEW [ IF EXISTS ] <имя> ALTER [ COLUMN ] <имя_столбца> DROP DEFAULT
ALTER VIEW [ IF EXISTS ] <имя> OWNER TO { <новый_владелец> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] <имя> RENAME [ COLUMN ] <имя_столбца> TO <новое_имя_столбца>
ALTER VIEW [ IF EXISTS ] <имя> RENAME TO <новое_имя>
ALTER VIEW [ IF EXISTS ] <имя> SET SCHEMA <новая_схема>
ALTER VIEW [ IF EXISTS ] <имя> SET ( <имя_параметра_представления> [= <значение_параметра_представления>] [, ... ] )
ALTER VIEW [ IF EXISTS ] <имя> RESET ( <имя_параметра_представления> [, ... ] )
Параметры ALTER VIEW

имя

Имя существующего представления (можно указать схему)

имя_столбца

Имя существующего столбца

новое_имя_столбца

Новое имя существующего столбца

IF EXISTS

Предотвращает возникновение ошибки, если представления не существует. В этом случае будет выведено уведомление

SET/DROP DEFAULT

Эти параметры устанавливают или удаляют значение по умолчанию для столбца. Значение столбца по умолчанию подставляется в любую команду INSERT или UPDATE, выполняемую в представлении, перед применением каких-либо правил или триггеров для представления. Эти значения по умолчанию имеют приоритет над любыми значениями по умолчанию из базовых отношений

новый_владелец

Имя пользователя, назначаемого новым владельцем представления

новое_имя

Новое имя представления

новая_схема

Новая схема представления

SET ( <имя_параметра_представления> [= <значение_параметра_представления>] [, …​ ] )

RESET ( <имя_параметра_представления> [, …​ ] )

Устанавливает или сбрасывает параметры представления. В настоящее время поддерживаются параметры:

  • check_option (enum) — изменяет параметр проверки представления. Допустимые значения local (локальная) или cascaded (каскадная).

  • security_barrier (boolean) — изменяет свойство представления security_barrier. Значение должно быть логическим: true или false

По историческим причинам ALTER TABLE также можно использовать с представлениями, но допустимы только те варианты ALTER TABLE, которые эквивалентны показанным выше.

Можно использовать ALTER VIEW, чтобы переименовать представление novels в all_novels_from_books.

ALTER VIEW novels RENAME TO all_novels_from_books;

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

ALTER VIEW all_novels_from_books ALTER COLUMN genre SET DEFAULT 'novel';

INSERT INTO all_novels_from_books (book_id, title, author_id, public_year, evaluation, price)
VALUES (12, 'Anna Karenina', 6, 1877, 8.9, 750);

Строка Anna Karenina вставлена со значением novel в столбце genre. Если вы попытаетесь вставить строку непосредственно в таблицу books, значение novel не будет использоваться как значение по умолчанию для столбца genre.

Удаление представления

Можно использовать команду DROP VIEW для удаления представления. Чтобы выполнить эту команду, нужно быть владельцем представления.

DROP VIEW [ IF EXISTS ] <имя> [, ...] [ CASCADE | RESTRICT ]
Параметры DROP VIEW

IF EXISTS

Предотвращает возникновение ошибки, если представления не существует. В этом случае будет выведено уведомление

имя

Имя представления (можно указать схему), подлежащего удалению

CASCADE

Удаляет объекты, которые зависят от представления (например, другие представления), и, в свою очередь, все объекты, зависящие от этих объектов (см. Dependency Tracking)

RESTRICT

Отказывает в удалении представления, если от него зависят какие-либо объекты. Это поведение по умолчанию

Пример:

DROP VIEW novels1;

Материализованные представления

Материализованные представления используют систему правил, аналогично представлениям, но сохраняют результаты в табличной форме. В отличие от представлений, материализованное представление хранит не только запрос, но и его результаты.

Разница между материализованным представлением и таблицей заключается в том, что материализованное представление не может быть обновлено напрямую. Его можно обновить с помощью оператора REFRESH MATERIALIZED VIEW.

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

Хотя доступ к данным, хранящимся в материализованном представлении, часто происходит намного быстрее, чем доступ к базовым таблицам напрямую или через обычное представление, данные могут быть не всегда актуальны. Материализованные представления можно использовать, когда некоторое устаревание данных вполне приемлемо. Например, агрегированная статистика, хранящаяся в материализованном представлении, может обновляться по таймеру в автоматическом режиме. Можно запланировать задачу по обновлению статистики с помощью оператора REFRESH MATERIALIZED VIEW.

Другой вариант использования материализованного представления — обеспечение более быстрого доступа к данным, получаемым из удаленной системы через обёртку внешних данных (foreign data wrapper). Даже если обёртка внешних данных не поддерживает индексы, в некоторых случаях индексы можно создать для материализованного представления.

Создание материализованного представления

Оператор CREATE MATERIALIZED VIEW определяет новое материализованное представление запроса. PostgreSQL выполняет запрос и заполняет представление в момент выполнения команды (если не используется WITH NO DATA). Временные материализованные представления не поддерживаются. Для выполнения CREATE MATERIALIZED VIEW требуется привилегия CREATE на схему, используемую для материализованного представления.

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <имя>
    [ (<имя_столбца> [, ...] ) ]
    [ USING <метод> ]
    [ WITH ( <параметр_хранения> [= <значение>] [, ... ] ) ]
    [ TABLESPACE <табличное_пространство> ]
    AS <запрос>
    [ WITH [ NO ] DATA ]
Параметры CREATE MATERIALIZED VIEW

IF NOT EXISTS

Предотвращает возникновение ошибки, если представление с таким именем уже существует. В этом случае будет выведено уведомление

имя

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

имя_столбца

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

USING <метод>

Необязательное выражение, которое определяет табличный метод доступа, используемый для хранения содержимого материализованного представления. Этот метод должен быть типа TABLE. См. Table Access Method Interface Definition. В случае отсутствия этого указания выбирается метод доступа по умолчанию. За подробностями обратитесь к разделу default_table_access_method

WITH ( <параметр_хранения> [= <значение>] [, …​ ] )

Задаёт дополнительные параметры хранения для материализованного представления. Все параметры, поддерживаемые для CREATE TABLE, также поддерживаются для CREATE MATERIALIZED VIEW. Подробнее о них можно узнать в разделе Storage Parameters

TABLESPACE <табличное_пространство>

табличное_пространство — имя табличного пространства, в котором должно быть создано материализованное представление. Если не указано, используется default_tablespace

запрос

Команда SELECT, TABLE или VALUES. Эта команда будет выполняться с ограничениями по безопасности. В частности, будут запрещены вызовы функций, которые создают временные таблицы

WITH [NO] DATA

Указывает, будет ли материализованное представление заполнено в момент создания. Если материализованное представление не заполняется, оно помечается как нечитаемое, и к нему нельзя будет обратиться до выполнения REFRESH MATERIALIZED VIEW

Создайте материализованное представление на основе упомянутой выше таблицы books и следующей таблицы authors:

 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
  6 | Leo Tolstoy
  7 | Dan Simmons
  8 | Herbert Wells
CREATE MATERIALIZED VIEW bookshelf AS
SELECT
    b.book_id,
    b.title,
    b.public_year,
    a.name,
    b.genre
FROM books b, authors a
WHERE b.author_id = a.id;

Выведите содержимое bookshelf.

SELECT * FROM bookshelf;

Результат:

 book_id |                title                | public_year |        name         |  genre
---------+-------------------------------------+-------------+---------------------+---------
       1 | Mrs. Dalloway                       |        1925 | Virginia Woolf      | novel
       2 | To the Lighthouse                   |        1927 | Virginia Woolf      | novel
       3 | To Kill a Mockingbird               |        1960 | Harper Lee          | novel
       4 | The Great Gatsby                    |        1925 | F. Scott Fitzgerald | novel
       5 | The Lord of the Rings               |        1955 | J.R.R. Tolkien      | fantasy
       6 | 1984                                |        1949 | George Orwell       | sci-fi
       7 | The Hobbit, or There and Back Again |        1937 | J.R.R. Tolkien      | fantasy
       8 | War and Peace                       |        1869 | Leo Tolstoy         | novel
       9 | Hyperion                            |        1989 | Dan Simmons         | sci-fi
      10 | The Time Machine                    |        1895 | Herbert Wells       | sci-fi
      11 | The World Set Free                  |        1914 | Herbert Wells       | sci-fi
      12 | Anna Karenina                       |        1877 | Leo Tolstoy         | novel

Добавьте строку в таблицу books.

INSERT INTO books (book_id, title, author_id, public_year, genre, evaluation, price)
VALUES (13, 'Resurrection', 6, 1899, 'novel', 9.1, 460);

Отобразите материализованное представление bookshelf:

SELECT * FROM bookshelf;

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

 book_id |                title                | public_year |        name         |  genre
---------+-------------------------------------+-------------+---------------------+---------
       1 | Mrs. Dalloway                       |        1925 | Virginia Woolf      | novel
       2 | To the Lighthouse                   |        1927 | Virginia Woolf      | novel
       3 | To Kill a Mockingbird               |        1960 | Harper Lee          | novel
       4 | The Great Gatsby                    |        1925 | F. Scott Fitzgerald | novel
       5 | The Lord of the Rings               |        1955 | J.R.R. Tolkien      | fantasy
       6 | 1984                                |        1949 | George Orwell       | sci-fi
       7 | The Hobbit, or There and Back Again |        1937 | J.R.R. Tolkien      | fantasy
       8 | War and Peace                       |        1869 | Leo Tolstoy         | novel
       9 | Hyperion                            |        1989 | Dan Simmons         | sci-fi
      10 | The Time Machine                    |        1895 | Herbert Wells       | sci-fi
      11 | The World Set Free                  |        1914 | Herbert Wells       | sci-fi
      12 | Anna Karenina                       |        1877 | Leo Tolstoy         | novel

Выполните оператор REFRESH MATERIALIZED VIEW, чтобы обновить bookshelf и проверьте результат.

REFRESH MATERIALIZED VIEW bookshelf;

SELECT * FROM bookshelf;

Материализованное представление обновлено.

 book_id |                title                | public_year |        name         |  genre
---------+-------------------------------------+-------------+---------------------+---------
       1 | Mrs. Dalloway                       |        1925 | Virginia Woolf      | novel
       2 | To the Lighthouse                   |        1927 | Virginia Woolf      | novel
       3 | To Kill a Mockingbird               |        1960 | Harper Lee          | novel
       4 | The Great Gatsby                    |        1925 | F. Scott Fitzgerald | novel
       5 | The Lord of the Rings               |        1955 | J.R.R. Tolkien      | fantasy
       6 | 1984                                |        1949 | George Orwell       | sci-fi
       7 | The Hobbit, or There and Back Again |        1937 | J.R.R. Tolkien      | fantasy
       8 | War and Peace                       |        1869 | Leo Tolstoy         | novel
       9 | Hyperion                            |        1989 | Dan Simmons         | sci-fi
      10 | The Time Machine                    |        1895 | Herbert Wells       | sci-fi
      11 | The World Set Free                  |        1914 | Herbert Wells       | sci-fi
      12 | Anna Karenina                       |        1877 | Leo Tolstoy         | novel
      13 | Resurrection                        |        1899 | Leo Tolstoy         | novel

Изменение свойств материализованного представления

Можно использовать оператор ALTER MATERIALIZED VIEW, чтобы изменить различные вспомогательные свойства материализованного представления. Команда ALTER MATERIALIZED VIEW имеет следующий синтаксис:

ALTER MATERIALIZED VIEW [ IF EXISTS ] <имя>
    <действие> [, ... ]
ALTER MATERIALIZED VIEW <имя>
    [ NO ] DEPENDS ON EXTENSION <имя_расширения>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <имя>
    RENAME [ COLUMN ] <имя_столбца> TO <новое_имя_столбца>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <имя>
    RENAME TO <новое_имя>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <имя>
    SET SCHEMA <новая_схема>
ALTER MATERIALIZED VIEW ALL IN TABLESPACE <имя> [ OWNED BY <имя_роли> [, ... ] ]
    SET TABLESPACE <новое_табличное_пространство> [ NOWAIT ]

где <действие> одно из:

    ALTER [ COLUMN ] <имя_столбца> SET STATISTICS integer
    ALTER [ COLUMN ] <имя_столбца> SET ( <атрибут> = <значение> [, ... ] )
    ALTER [ COLUMN ] <имя_столбца> RESET ( <атрибут> [, ... ] )
    ALTER [ COLUMN ] <имя_столбца> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ALTER [ COLUMN ] <имя_столбца> SET COMPRESSION <метод_сжатия>
    CLUSTER ON <имя_индекса>
    SET WITHOUT CLUSTER
    SET TABLESPACE <новое_табличное_пространство>
    SET ( <параметр_хранения> [= <значение>] [, ... ] )
    RESET ( <параметр_хранения> [, ... ] )
    OWNER TO { <новый_владелец> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
Параметры ALTER MATERIALIZED VIEW

имя

Имя существующего материализованного представления (можно указать схему)

имя_столбца

Имя нового или существующего столбца

имя_расширения

Имя расширения, от которого будет зависеть материализованное представление (или не будет, если указано NO). Материализованное представление, отмеченное как зависимое от расширения, удаляется при удалении расширения

новое_имя_столбца

Новое имя существующего столбца

новый_владелец

Имя пользователя, назначаемого новым владельцем материализованного представления

новое_имя

Новое имя материализованного представления

новая_схема

Новая схема материализованного представления

Например, с помощью ALTER MATERIALIZED VIEW можно переименовать представление bookshelf в books_authors.

ALTER MATERIALIZED VIEW bookshelf RENAME TO books_authors;

Выполнить ALTER MATERIALIZED VIEW может только владелец материализованного представления. Чтобы сменить схему материализованного представления, необходимо также иметь право CREATE в новой схеме. Чтобы сменить владельца, требуется быть членом новой роли, а эта роль должна иметь право CREATE в схеме материализованного представления.

Формы и действия выражения ALTER MATERIALIZED VIEW являются подмножеством форм и действий ALTER TABLE и имеют то же значение применительно к материализованным представлениям.

Удаление материализованного представления

Используйте оператор DROP MATERIALIZED VIEW, чтобы удалить материализованное представление. Для выполнения этой команды вы должны быть владельцем материализованного представления.

DROP MATERIALIZED VIEW [ IF EXISTS ] <имя> [, ...] [ CASCADE | RESTRICT ]
Параметры DROP MATERIALIZED VIEW

IF EXISTS

Предотвращает возникновение ошибки, если материализованного представления не существует. В этом случае будет выведено уведомление

имя

Имя материализованного представления (можно указать схему), подлежащего удалению

CASCADE

Удаляет объекты, которые зависят от материализованного представления (например, другие материализованные и обыкновенные представления), и, в свою очередь, все объекты, зависящие от этих объектов (см. Dependency Tracking)

RESTRICT

Отказывает в удалении материализованного представления, если от него зависят какие-либо объекты. Это поведение по умолчанию

Пример:

DROP MATERIALIZED VIEW books_authors;

Сравнение представлений и материализованных представлений

Основные различия между представлением и материализованным представлением следующие:

  • Представление сохраняет только определяющий его запрос. Материализованное представление хранит определяющий запрос и его результат.

  • Каждый раз при обращении к обыкновенному представлению выполняется его определяющий запрос. При обращении к материализованному представлению оно возвращает ранее сохраненный результат. Материализованное представление может возвращать устаревшие данные, если были изменены его базовые таблицы или представления. Необходимо выполнить оператор REFRESH MATERIALIZED VIEW, чтобы обновить материализованное представление.

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

Получение информации о представлениях и материализованных представлениях

Можно использовать метакоманду psql \dv или \dv+, чтобы получить список представлений.

$ \dv

Результат:

                List of relations
 Schema |         Name          | Type |  Owner
--------+-----------------------+------+----------
 public | all_novels_from_books | view | postgres
 public | novels2               | view | postgres
 public | test                  | view | postgres
(3 rows)

Метакоманда \dv+ предоставляет более подробную информацию.

 Schema |         Name          | Type |  Owner   | Persistence |  Size   | Description
--------+-----------------------+------+----------+-------------+---------+-------------
 public | all_novels_from_books | view | postgres | permanent   | 0 bytes |
 public | novels2               | view | postgres | permanent   | 0 bytes |
 public | test                  | view | postgres | permanent   | 0 bytes |
(3 rows)

Можно использовать метакоманду psql \dm или \dm+, чтобы получить список материализованных представлений.

Метакоманда \dm+ предоставляет более подробную информацию.

 Schema |   Name    |       Type        |  Owner   | Persistence | Access method |    Size    | Description
--------+-----------+-------------------+----------+-------------+---------------+------------+-------------
 public | bookshelf | materialized view | postgres | permanent   | heap          | 8192 bytes |
(1 row)

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

SELECT table_catalog, table_schema, table_name, view_definition, check_option
FROM INFORMATION_SCHEMA.views
WHERE table_schema = ANY (current_schemas(false));

Условие table_schema = ANY (current_schemas(false)) исключает из вывода системные представления.

Результат:

 table_catalog | table_schema |      table_name       |                   view_definition                   | check_option
---------------+--------------+-----------------------+-----------------------------------------------------+--------------
 bookstore     | public       | test                  |  SELECT 'Test'::text;                               | NONE
 bookstore     | public       | novels2               |  SELECT all_novels_from_books.book_id,             +| CASCADED
               |              |                       |     all_novels_from_books.title,                   +|
               |              |                       |     all_novels_from_books.author_id,               +|
               |              |                       |     all_novels_from_books.public_year,             +|
               |              |                       |     all_novels_from_books.genre,                   +|
               |              |                       |     all_novels_from_books.evaluation,              +|
               |              |                       |     all_novels_from_books.price                    +|
               |              |                       |    FROM all_novels_from_books                      +|
               |              |                       |   WHERE (all_novels_from_books.public_year < 1959); |
 bookstore     | public       | all_novels_from_books |  SELECT books.book_id,                             +| NONE
               |              |                       |     books.title,                                   +|
               |              |                       |     books.author_id,                               +|
               |              |                       |     books.public_year,                             +|
               |              |                       |     books.genre,                                   +|
               |              |                       |     books.evaluation,                              +|
               |              |                       |     books.price                                    +|
               |              |                       |    FROM books                                      +|
               |              |                       |   WHERE ((books.genre)::text = 'novel'::text);      |
(3 rows)

Для отображения параметров материализованных представлений используйте pg_matviews.

SELECT * from pg_matviews;

Результат:

 schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated |          definition
------------+-------------+--------------+------------+------------+-------------+-------------------------------
 public     | bookshelf   | postgres     |            | f          | t           |  SELECT b.book_id,           +
            |             |              |            |            |             |     b.title,                 +
            |             |              |            |            |             |     b.public_year,           +
            |             |              |            |            |             |     a.name,                  +
            |             |              |            |            |             |     b.genre                  +
            |             |              |            |            |             |    FROM books b,             +
            |             |              |            |            |             |     author a                 +
            |             |              |            |            |             |   WHERE (b.author_id = a.id);
(1 row)

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

PostgreSQL включает встроенные системные представления. Некоторые представления предоставляют доступ к часто используемым запросам к системным каталогам, другие возвращают параметры внутреннего состояния сервера. Например, pg_settings отображает runtime-параметры сервера, pg_roles содержит информацию о ролях базы данных, а pg_matviews — о материализованных представлениях.

Системные представления
Название Описание

pg_available_extensions

Доступные расширения

pg_available_extension_versions

Доступные версии расширений

pg_backend_memory_contexts

Контекст памяти обслуживающего процесса

pg_config

Параметры конфигурации, используемые при компиляции

pg_cursors

Доступные курсоры

pg_file_settings

Сводка содержимого файла конфигурации

pg_group

Группы пользователей баз данных

pg_hba_file_rules

Сводка содержимого файла конфигурации аутентификации клиентов

pg_indexes

Индексы

pg_locks

Блокировки, установленные или ожидаемые в данный момент

pg_matviews

Материализованные представления

pg_policies

Политики

pg_prepared_statements

Подготовленные операторы

pg_prepared_xacts

Подготовленные транзакции

pg_publication_tables

Публикации и связанные с ними таблицы

pg_replication_origin_status

Информация об источниках репликации, включая данные прогресса репликации

pg_replication_slots

Информация о слотах репликации

pg_roles

Роли

pg_rules

Правила

pg_seclabels

Метки безопасности

pg_sequences

Последовательности

pg_settings

Значения параметров

pg_shadow

Пользователи базы данных

pg_shmem_allocations

Блоки, выделенные в общей памяти

pg_stats

Статистика планировщика

pg_stats_ext

Расширенная статистика планировщика

pg_stats_ext_exprs

Расширенная статистика планировщика по выражениям

pg_tables

Таблицы

pg_timezone_abbrevs

Аббревиатуры часовых поясов

pg_timezone_names

Имена часовых поясов

pg_user

Пользователи базы данных

pg_user_mappings

Сопоставления пользователей

pg_views

Представления

PostgreSQL также включает несколько дополнительных представлений, которые обеспечивают доступ к результатам сборщика статистики, см. Collected statistics views.

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

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней