Une colonne générée est une colonne spéciale, toujours calculée à partir d'autres colonnes. Elle est donc aux colonnes ce qu'une vue est aux tables. Il y a deux types de colonnes générées : stockée et virtuelle. Une colonne générée stockée est calculée quand elle est écrite (insérée ou mise à jour) et occupe de l'espace de stockage comme si elle était une colonne normale. Une colonne virtuelle générée n'occupe pas d'espace et est calculée à la lecture. Une colonne générée virtuelle est donc équivalente à une vue, et une colonne générée stockée est équivalente à une vue matérialisée (sauf qu'elle sera toujours mise à jour automatiquement).
Pour créer une colonne générée, utilisez la clause GENERATED
ALWAYS AS de la commande CREATE TABLE, par
exemple :
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.
On ne peut écrire directement dans une colonne générée. Une valeur ne peut
pas y être affectée dans les commandes INSERT ou
UPDATE, mais le mot-clé DEFAULT peut
l'être.
Voyons les différences entre une colonne avec une valeur par défaut et une
colonne générée. La colonne par défaut est calculée une seule fois à la
première insertion de la ligne si aucune autre valeur n'est fournie ;
une colonne générée est mise à jour à chaque fois que la ligne change et
on ne peut y déroger. Une valeur par défaut d'une colonne ne peut se
référer à d'autres colonnes de la table ; mais c'est ce que fait
normalement une expression générée. Une valeur par défaut d'une colonne
peut utiliser des fonctions volatiles, par exemple
random() ou des fonctions se référant au temps
actuel ; ce n'est pas permis pour les colonnes générées.
Il existe plusieurs restrictions dans la définition des colonnes générées et des tables qui les utilisent :
L'expression pour générer les valeurs ne peut utiliser que des fonctions immutables, ne peut pas utiliser de sous-requêtes, ni référencer d'aucune manière quoi que ce soit hors de la ligne en cours.
Une expression ne peut référencer une autre colonne générée.
Une expression ne peut référencer une colonne système, sauf
tableoid.
Une colonne générée ne peut avoir une valeur par défaut ou être définie comme colonne identité.
Une colonne générée ne peut faire partie d'une clé de partitionnement.
Les tables distantes peuvent porter des colonnes générées. Voir CREATE FOREIGN TABLE pour les détails.
Pour l'héritage et le partitionnement :
Si une colonne parent est une colonne générée, la colonne enfant doit aussi être une colonne générée de même type (enregistrée ou virtuelle). Néanmoins, la colonne enfant peut avoir une expression de génération différente.
Pour les colonnes générées enregistrées, l'expression de génération qui est réellement appliquée lors de l'insertion ou de la mise à jour d'une ligne est celle qui est associée avec la table qui contiendra physiquement la ligne. (Ceci est contraire au comportement pour la valeur par défaut d'une colonne : pour ces dernières, la valeur par défaut appliquée est celle associée avec la table nommée dans la requête.) For virtual generated columns, the generation expression of the table named in the query applies when a table is read.
Si une colonne parent n'est pas une colonne générée, sa colonne enfant ne doit pas non plus être une colonne générée.
Pour les tables héritées, si vous écrivez une définition de colonne
de la table enfant sans clause
GENERATED dans CREATE TABLE
... INHERITS, alors sa clause GENERATED
sera automatiquement copiée du parent. ALTER TABLE
... INHERIT insistera que les colonnes parents et enfants
correspondant déjà au statut de génération, mais il n'obligera pas
à ce que les expressions de génération correspondent.
De façon similaire pour les tables partitionnées, si vous écrivez la
définition d'une colonne enfant sans la clause
GENERATED clause dans
CREATE TABLE ... PARTITION OF, alors la clause
GENERATED sera automatiquement copiée du parent.
ALTER TABLE ... ATTACH PARTITION insistera pour
que les colonnes parents et enfants correspondent déjà au niveau
du statut de génération, mais il ne reclamera pas que les expressions
de génération correspondent.
Dans le cas de plusieurs héritages, si une des colonnes parents est une colonne générée, alors toutes les colonnes parents doivent être des colonnes générées. Si elles n'ont pas toutes la même expression de génération, alors l'expression désirée pour l'enfant doit être indiquée explicitement.
D'autres considérations s'appliquent à l'utilisation des colonnes générées.
Les colonnes générées maintiennent les droits d'accès séparément des colonnes sur lesquelles elles sont basées. On peut donc s'arranger pour qu'un rôle défini puisse lire une colonne générée mais pas la colonne de base sous-jacente.
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 INVOKER
or SECURITY DEFINER.
Conceptuellement, les colonnes générées sont mises à jour après le
déclenchement des triggers BEFORE. Les changements
dans les colonnes de base au sein d'un trigger
BEFORE seront donc répercutés dans les colonnes
générées. Mais à l'inverse, il n'est pas permis d'accéder aux colonnes
générées dans les triggers BEFORE.
Generated columns are allowed to be replicated during logical replication
according to the CREATE PUBLICATION parameter
publish_generated_columns or by including them
in the column list of the CREATE PUBLICATION command.
This is currently only supported for stored generated columns.
See Section 29.6 for details.