5.4. Генерируемые столбцы #
Генерируемый столбец является столбцом особого рода, который всегда вычисляется из других. Таким образом, для столбцов он является тем же, чем представление для таблицы. Есть два типа генерируемых столбцов: хранимые и виртуальные. Хранимый генерируемый столбец вычисляется при записи (добавлении или изменении) и занимает место в таблице так же, как и обычный столбец. Виртуальный генерируемый столбец не занимает места и вычисляется при чтении. Поэтому можно сказать, что виртуальный генерируемый столбец похож на представление, а хранимый генерируемый столбец — на материализованное представление (за исключением того, что он всегда обновляется автоматически).
Чтобы создать генерируемый столбец, воспользуйтесь предложением GENERATED ALWAYS AS в команде CREATE TABLE, например:
CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54)
); По умолчанию генерируемый столбец является виртуальным. Чтобы указать тип хранения явно, используйте ключевые слова VIRTUAL или STORED. За подробностями обратитесь к CREATE TABLE.
Произвести запись непосредственно в генерируемый столбец нельзя. Поэтому в командах INSERT или UPDATE нельзя задать значение для таких столбцов, хотя ключевое слово DEFAULT указать можно.
Примите к сведению следующие отличия генерируемых столбцов от столбцов со значением по умолчанию. Значение столбца по умолчанию вычисляется один раз, когда в таблицу впервые вставляется строка и никакое другое значение не задано; значение же генерируемого столбца может меняться при изменении строки и не может быть переопределено. Выражение значения по умолчанию не может обращаться к другим столбцам таблицы, а генерирующее выражение, напротив, обычно обращается к ним. В выражении значения по умолчанию могут вызываться изменчивые функции, например, random() или функции, зависящие от времени, а для генерируемых столбцов это не допускается.
С определением генерируемых столбцов и их содержащими таблицами связан ряд ограничений и особенностей:
В генерирующем выражении могут использоваться только постоянные функции и не могут фигурировать подзапросы или ссылки на какие-либо значения, не относящиеся к данной строке.
Генерирующее выражение не может обращаться к другому генерируемому столбцу.
Генерирующее выражение не может обращаться к системным столбцам, за исключением
tableoid.Виртуальный генерируемый столбец не может иметь пользовательский тип, а его генерирующее выражение не должно ссылаться на пользовательские функции или типы, то есть должно использовать только встроенные функции и типы. Это ограничение действует и косвенным образом, например, для функций или типов, лежащих в основе операторов или приведений. (Это ограничение не распространяется на хранимые генерируемые столбцы.)
Для генерируемого столбца нельзя задать значение по умолчанию или свойство идентификации.
Генерируемый столбец не может быть частью ключа секционирования.
Генерируемые столбцы могут содержаться в сторонних таблицах. За подробностями обратитесь к CREATE FOREIGN TABLE.
Применительно к наследованию и секционированию:
Если родительский столбец является генерируемым, дочерний столбец также должен быть генерируемым столбцом того же типа (хранимым или виртуальным); однако дочерний столбец может иметь другое генерирующее выражение.
Для хранимых генерируемых столбцов генерирующее выражение, которое фактически применяется во время вставки или изменения строки, связано с таблицей, в которой физически находится строка. (Это отличается от поведения столбцов со значением по умолчанию: для них применяется значение по умолчанию, которое связано с таблицей, указанной в запросе.) Для виртуальных генерируемых столбцов при чтении таблицы применяется генерирующее выражение из таблицы, указанной в запросе.
Если родительский столбец не является генерируемым, его дочерний столбец также должен быть негенерируемым.
Для унаследованных таблиц, если в определении дочернего столбца нет предложения
GENERATEDвCREATE TABLE ... INHERITS, то предложениеGENERATEDбудет автоматически копироваться из родительского столбца. Для командыALTER TABLE ... INHERITтребуется, чтобы родительский и дочерний столбцы уже совпадали в отношении состояния генерации, но не требуется совпадения их генерирующих выражений.Аналогично для секционированных таблиц, если в определении дочернего столбца нет предложения
GENERATEDвCREATE TABLE ... INHERITS, то его предложениеGENERATEDбудет автоматически копироваться у родительского столбца. Для командыALTER TABLE ... ATTACH PARTITIONтребуется, чтобы родительский и дочерний столбцы уже совпадали в отношении состояния генерации, но не требует совпадения их генерирующих выражений.В случае множественного наследования, если один родительский столбец является генерируемым, все соответствующие ему столбцы в иерархии наследования также должны быть генерируемыми. Если генерирующее выражение не одинаковое для всех столбцов, то желаемое выражение для дочернего столбца должно быть указано явно.
Дополнительные замечания касаются использования генерируемых столбцов.
Права доступа к генерируемым столбцам существуют отдельно от прав для нижележащих базовых столбцов. Поэтому их можно организовать так, чтобы определённый пользователь мог прочитать генерируемый столбец, но не нижележащие базовые столбцы.
Для виртуальных генерируемых столбцов это полностью безопасно только в том случае, если генерирующее выражение использует исключительно герметичные функции (см. CREATE FUNCTION), однако система этого не контролирует.
Права на вызов функций, используемых в генерирующих выражениях, проверяются в момент фактического выполнения выражения — при записи или чтении соответственно — так, как если бы генерирующее выражение вызывалось напрямую из запроса с использованием генерируемого столбца. Пользователь, использующий генерируемый столбец, должен иметь права на вызов всех функций, применяемых в генерирующем выражении. Функции в генерирующем выражении выполняются с правами пользователя, выполняющего запрос, либо с правами владельца функции, в зависимости от того, определены ли функции как
SECURITY INVOKERилиSECURITY DEFINER.Генерируемые столбцы, в соответствии с концепцией, пересчитываются после выполнения триггеров
BEFORE. Вследствие этого, в генерируемых столбцах будут отражаться изменения, производимые триггеромBEFOREв базовых столбцах, а обращаться в коде такого триггера к генерируемым столбцам, напротив, нельзя.Генерируемые столбцы могут участвовать в логической репликации в соответствии с параметром
CREATE PUBLICATIONpublish_generated_columnsлибо путём явного указания в списке столбцов командыCREATE PUBLICATION. В настоящее время это поддерживается только для хранимых генерируемых столбцов. За подробностями обратитесь к Разделу 30.6.
5.4. Generated Columns #
A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically).
To create a generated column, use the GENERATED ALWAYS AS clause in CREATE TABLE, for example:
CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54)
);
A generated column is by default of the virtual kind. Use the keywords VIRTUAL or STORED to make the choice explicit. See CREATE TABLE for more details.
A generated column cannot be written to directly. In INSERT or UPDATE commands, a value cannot be specified for a generated column, but the keyword DEFAULT may be specified.
Consider the differences between a column with a default and a generated column. The column default is evaluated once when the row is first inserted if no other value was provided; a generated column is updated whenever the row changes and cannot be overridden. A column default may not refer to other columns of the table; a generation expression would normally do so. A column default can use volatile functions, for example random() or functions referring to the current time; this is not allowed for generated columns.
Several restrictions apply to the definition of generated columns and tables involving generated columns:
The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.
A generation expression cannot reference another generated column.
A generation expression cannot reference a system column, except
tableoid.A virtual generated column cannot have a user-defined type, and the generation expression of a virtual generated column must not reference user-defined functions or types, that is, it can only use built-in functions or types. This applies also indirectly, such as for functions or types that underlie operators or casts. (This restriction does not exist for stored generated columns.)
A generated column cannot have a column default or an identity definition.
A generated column cannot be part of a partition key.
Foreign tables can have generated columns. See CREATE FOREIGN TABLE for details.
For inheritance and partitioning:
If a parent column is a generated column, its child column must also be a generated column of the same kind (stored or virtual); however, the child column can have a different generation expression.
For stored generated columns, the generation expression that is actually applied during insert or update of a row is the one associated with the table that the row is physically in. (This is unlike the behavior for column defaults: for those, the default value associated with the table named in the query applies.) For virtual generated columns, the generation expression of the table named in the query applies when a table is read.
If a parent column is not a generated column, its child column must not be generated either.
For inherited tables, if you write a child column definition without any
GENERATEDclause inCREATE TABLE ... INHERITS, then itsGENERATEDclause will automatically be copied from the parent.ALTER TABLE ... INHERITwill insist that parent and child columns already match as to generation status, but it will not require their generation expressions to match.Similarly for partitioned tables, if you write a child column definition without any
GENERATEDclause inCREATE TABLE ... PARTITION OF, then itsGENERATEDclause will automatically be copied from the parent.ALTER TABLE ... ATTACH PARTITIONwill insist that parent and child columns already match as to generation status, but it will not require their generation expressions to match.In case of multiple inheritance, if one parent column is a generated column, then all parent columns must be generated columns. If they do not all have the same generation expression, then the desired expression for the child must be specified explicitly.
Additional considerations apply to the use of generated columns.
Generated columns maintain access privileges separately from their underlying base columns. So, it is possible to arrange it so that a particular role can read from a generated column but not from the underlying base columns.
For virtual generated columns, this is only fully secure if the generation expression uses only leakproof functions (see CREATE FUNCTION), but this is not enforced by the system.
Privileges of functions used in generation expressions are checked when the expression is actually executed, on write or read respectively, as if the generation expression had been called directly from the query using the generated column. The user of a generated column must have permissions to call all functions used by the generation expression. Functions in the generation expression are executed with the privileges of the user executing the query or the function owner, depending on whether the functions are defined as
SECURITY INVOKERorSECURITY DEFINER.Generated columns are, conceptually, updated after
BEFOREtriggers have run. Therefore, changes made to base columns in aBEFOREtrigger will be reflected in generated columns. But conversely, it is not allowed to access generated columns inBEFOREtriggers.Generated columns are allowed to be replicated during logical replication according to the
CREATE PUBLICATIONparameterpublish_generated_columnsor by including them in the column list of theCREATE PUBLICATIONcommand. This is currently only supported for stored generated columns. See Section 30.6 for details.
