CREATE VIEW — Définir une vue
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEWnom
[ (nom_colonne
[, ...] ) ] [ WITH (nom_option_vue
[=valeur_option_vue
] [, ... ] ) ] ASrequête
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
CREATE VIEW
définit une vue d'après une requête. La
vue n'est pas matérialisée physiquement. Au lieu de cela, la requête
est lancée chaque fois qu'une vue est utilisée dans une requête.
CREATE OR REPLACE VIEW
a la même finalité, mais
si une vue du même nom existe déjà, elle est remplacée. La nouvelle requête
doit générer les mêmes colonnes que celles de l'ancienne requête (c-est-à-dire
les mêmes noms de colonnes dans le même ordre avec les mêmes types de données).
Par contre, elle peut ajouter des colonnes supplémentaires en fin de liste.
Les traitements qui donnent les colonnes en sortie pourraient être complètement
différents.
Si un nom de schéma est donné (par exemple
CREATE VIEW monschema.mavue ...
),
alors la vue est créée dans ce schéma.
Dans le cas contraire, elle est créée dans le schéma courant. Les vues
temporaires existent dans un schéma spécial. Il n'est donc pas nécessaire
de fournir de schéma pour les vues temporaires. Le
nom de la vue doit être différent du nom de toute autre vue, table, séquence,
index ou table distante du même schéma.
TEMPORARY
ou TEMP
La vue est temporaire. Les vues temporaires sont automatiquement supprimées en fin de session. Les relations permanentes qui portent le même nom ne sont plus visibles pour la session tant que la vue temporaire existe, sauf s'il y est fait référence avec le nom du schéma.
Si l'une des tables référencées par la vue est temporaire, la vue est alors elle-aussi
temporaire (que TEMPORARY
soit spécifié ou non).
RECURSIVE
Crée une vue récursive. La syntaxe
CREATE RECURSIVE VIEW [schéma
. ]nom
(colonnes
) AS SELECT...
;
est équivalente à
CREATE VIEW [schéma
. ]nom
AS WITH RECURSIVEnom
(colonnes
) AS (SELECT...
) SELECTcolonne
FROMnom
;
Une liste de noms de colonne doit être spécifiée pour la vue récursive.
nom
Le nom de la vue à créer (éventuellement qualifié du nom du schéma).
nom de colonne
Une liste optionnelle de noms à utiliser pour les colonnes de la vue. Si elle n'est pas donnée, le nom des colonnes est déduit de la requête.
WITH ( nom de l'option de vue
[= valeur de l'option
] [, ... ] )
Cette clause spécifie des paramètres optionnels pour une vue. Les paramètres supportés sont les suivants :
check_option
(string
)
Ce paramètre peut avoir soit local
soit
cascaded
, et est l'équivalent de spécifier
WITH [ CASCADED | LOCAL ] CHECK OPTION
(voir
ci-dessous). Cette option peut être modifiée sur des vues
existantes en utilisant ALTER VIEW.
security_barrier
(boolean
)Ceci doit être utilisé si la vue a pour but de fournir une sécurité au niveau ligne. Voir Section 41.5 pour plus de détails.
requête
Une commande SELECT ou VALUES qui fournira les colonnes et lignes de la vue.
WITH [ CASCADED | LOCAL ] CHECK OPTION
Cette option contrôle le comportement des vues automatiquement modifiables.
Quand cette option est spécifiée, les commandes INSERT
et UPDATE
sur la vue seront vérifiées pour s'assurer que
les nouvelles lignes satisfont la condition définie dans la vue (autrement
dit, les nouvelles lignes sont vérifiées pour s'assurer qu'elles sont
visibles par la vue). Dans le cas contraire, la mise à jour est rejetée.
Si l'option CHECK OPTION
n'est pas indiquée, les commandes
INSERT
et UPDATE
sur la vue sont
autorisées à créer des lignes qui ne sont pas visibles avec la vue. Les
options de vérification suivantes sont supportées :
LOCAL
Les nouvelles lignes sont seulement vérifiées avec les conditions
définies directement dans la vue. Toute condition définie dans les
relations sous-jacentes ne sont pas vérifiées (sauf si elles disposent
elles-même de l'option CHECK OPTION
).
CASCADED
Les nouvelles lignes sont vérifiées avec les conditions de la vue et
de toutes les relations sous-jacentes. Si l'option CHECK
OPTION
est précisée, et que ni LOCAL
ni
CASCADED
ne le sont, alors
CASCADED
est supposé.
L'option CHECK OPTION
ne peut pas être utilisé dans les
vues RECURSIVE
.
Il faut noter que l'option CHECK OPTION
est seulement
acceptée sur les vues qui sont automatiquement modifiables, et n'ont pas
de triggers INSTEAD OF
ou de règles
INSTEAD
. Si une vue modifiable automatiquement est
définie au-dessus d'une vue de base qui dispose de triggers
INSTEAD OF
, alors l'option LOCAL CHECK
OPTION
peut être utilisé pour vérifier les conditions de la
vue automatiquement modifiable mais les conditions de la vue de base
comprenant des triggers INSTEAD OF
ne seront pas
vérifiées (une option de vérification en cascade ne continuera pas après
vue avec trigger et toute option de vérification définie directement sur
une vue automatiquement modifiable sera ignorée). Si la vue ou une des
relations sous-jacentes a une règle INSTEAD
qui cause
la réécriture des commandes INSERT
ou
UPDATE
, alors toutes les options de vérification
seront ignorées dans la requête réécrite, ainsi que toutes les
vérifications provenant de vues automatiquement modifiables définies au
niveau haut d'une relation avec la règle INSTEAD
.
L'instruction DROP VIEW est utilisée pour supprimer les vues.
Il est important de s'assurer que le nom et le type des colonnes de la vue correspondent à ce qui est souhaité. Ainsi :
CREATE VIEW vista AS SELECT 'Hello World';
est une mauvaise façon de procéder car le nom de la colonne vaudra par
défaut?column?
; de plus, le type de donnée de la colonne vaudra par défaut
text
, ce qui pourrait ne pas être ce que vous voulez. Un
meilleur style pour une chaîne litérale dans le résultat d'une vue est
quelque chose comme :
CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
L'accès aux tables référencées dans la vue est déterminé par les droits du propriétaire de la vue. Dans certains cas, cela peut être utilisé pour fournir un accès sécurisé. Cependant, toutes les vues ne sont pas sécurisables ; voir Section 41.5 pour des détails. Les fonctions appelées dans la vue sont traitées de la même façon que si elles avaient été appelées directement dans la requête utilisant la vue. Du coup, l'utilisateur d'une vue doit avoir les droits pour appeler toutes les fonctions utilisées par la vue.
Quand CREATE OR REPLACE VIEW
est utilisé sur une vue
existante, seule la règle SELECT définissant la vue est modifiée. Les
autres propriétés, comme les droits, le propriétaire et les règles autres
que le SELECT, ne sont pas modifiées. Vous devez être le propriétaire de
la vue pour la remplacer (ceci incluant aussi les membres du rôle
propriétaire).
Les vues simples sont automatiquement modifiables : le système autorise
l'utilisation des commandes INSERT
,
UPDATE
et DELETE
sur les vues comme
sur les tables. Une vue est modifiable automatiquement si elle satisfait les
conditions suivantes :
La vue doit avoir exactement une entrée (une table ou une autre vue
modifiable) dans la liste FROM
.
La définition de la vue ne doit pas contenir de clauses
WITH
, DISTINCT
, GROUP
BY
, HAVING
, LIMIT
ou
OFFSET
au niveau le plus haut.
La définition de la vue ne doit pas contenir d'opérations sur des
ensembles (UNION
, INTERSECT
ou
EXCEPT
) au niveau le plus haut.
La liste de sélection de la vue ne doit pas contenir d'agrégats, de fonctions de fenêtrage ou de fonctions renvoyant des ensembles de lignes.
Une vue à mise à jour automatique peut contenir un mélange de colonnes
modifiables et non modifiables. Une colonne est modifiable si elle est
une référence simple à une colonne modifiable de la relation sous-jacente.
Dans le cas contraire, la colonne est en lecture seule et une erreur sera
levée si une instruction INSERT
ou
UPDATE
tente d'assigner une valeur à cette colonne.
Si la vue est modifiable automatiquement, le système convertira automatiquement
toute commande INSERT
, UPDATE
ou
DELETE
sur la vue dans la commande correspondante sur la
relation sous-jacente. Les requêtes INSERT
qui ont une
clause ON CONFLICT UPDATE
sont supportées.
Si une vue modifiable automatiquement contient une condition
WHERE
, la condition restreint les lignes modifiables
dans la relation de base par une commande UPDATE
ou
DELETE
. Néanmoins, un UPDATE
peut
modifier une ligne qui ne satisfait plus la condition
WHERE
, et du coup qui n'est plus visible par la vue.
De la même façon, une commande INSERT
peut insérer des
lignes dans la relation de base qui ne satisfont par la condition
WHERE
et qui, du coup, ne sont pas visibles via la vue
(ON CONFLICT UPDATE
pourrait aussi impacter une ligne non
visible au travers de la vue).
La clause CHECK OPTION
peut être utilisée pour empêcher
que les commandes INSERT
et UPDATE
créent de telles lignes qui ne sont pas visibles au travers de la vue.
Si une vue modifiable automatiquement est marquée avec la propriété
security_barrier
, alors toutes les conditions de
la clause WHERE
(et toutes les conditions utilisant des
opérateurs marqués LEAKPROOF
) seront toujours évaluées
avant les conditions ajoutées par l'utilisateur de la vue. Voir Section 41.5 pour les détails complets. Notez qu'à cause de
ce comportement, les lignes qui ne sont pas renvoyées (parce qu'elles ne
satisfont pas les conditions de la clause WHERE
de
l'utilisateur) pourraient quand même se trouver bloquées.
EXPLAIN
peut être utilisé pour voir les conditions
appliquées au niveau de la relation (pas de verrou des lignes dans ce cas)
et celles qui ne le sont pas.
Une vue plus complexe qui ne satisfait par toutes les conditions ci-dessus
est par défaut en lecture seule : le système ne permettra ni insertion,
ni mise à jour, ni suppression sur la vue. Vous pouvez obtenir le même effet
qu'une vue modifiable en créant des triggers INSTEAD OF
sur la vue. Ces triggers doivent convertir l'insertion, ... tentée sur la
vue par l'action appropriée sur les autres tables. Pour plus d'informations,
voir CREATE TRIGGER. Une autre possibilité revient à
créer des règles (voir CREATE RULE). Cependant, en
pratique, les triggers sont plus simples à comprendre et à utiliser
correctement.
Notez que l'utilisateur réalisant l'insertion, la mise à jour ou la suppression sur la vue doit avoir les droits correspondants sur la vue. De plus, le propriétaire de la vue doit avoir les droits correspondants sur les relations sous-jacentes mais l'utilisateur réalisant la mise à jour n'a pas besoin de droits sur les relations sous-jacentes (voir Section 41.5).
Créer une vue composée des comédies :
CREATE VIEW comedies AS SELECT * FROM films WHERE genre = 'Comédie';
Cette requête crée une vue contenant les colonnes de la table
film
au moment de la création de la vue. Bien que l'étoile
(*
) soit utilisée pour créer la vue, les colonnes
ajoutées par la suite à la table film
ne feront pas
partie de la vue.
Créer une vue avec l'option LOCAL CHECK OPTION
:
CREATE VIEW comedies_universelles AS SELECT * FROM comedies WHERE classification = 'U' WITH LOCAL CHECK OPTION;
Ceci créera une vue basée sur la vue comedies
, ne montrant
que les films pour lesquels kind = 'Comedy'
et
classification = 'U'
. Toute tentative d'INSERT
ou d'UPDATE
d'une ligne dans la vue sera rejeté si la
nouvelle ligne ne correspond pas à classification = 'U'
,
mais le type du film (colonne genre
) ne sera pas vérifié.
Créer une vue avec CASCADED CHECK OPTION
:
CREATE VIEW pg_comedies AS SELECT * FROM comedies WHERE classification = 'PG' WITH CASCADED CHECK OPTION;
Ceci créera une vue qui vérifie les colonnes kind
et
classification
de chaque nouvelle ligne.
Créer une vue avec un ensemble de colonnes modifiables et non modifiables :
CREATE VIEW comedies AS SELECT f.*, code_pays_a_nom(f.code_pays) AS pays, (SELECT avg(r.score) FROM utilisateurs_score r WHERE r.film_id = f.id) AS score_moyen FROM films f WHERE f.genre = 'Comedy';
Cette vue supportera les commandes INSERT
,
UPDATE
et DELETE
. Toutes les colonnes
de la table films
seront modifiables, alors que les
colonnes calculées, pays
et score_moyen
seront en lecture seule.
Créer une vue récursive consistant en des nombres 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;
Notez que, bien que le nom de la vue récursive est qualifié du schéma dans
cette commande CREATE
, sa propre référence interne n'est
pas qualifiée du schéma. Ceci est dû au fait que le nom, implicitement
créé, de la CTE ne peut pas être qualifié d'un schéma.
Le standard SQL spécifie quelques possibilités supplémentaires pour
l'instruction CREATE VIEW
:
CREATE VIEWnom
[ (nom_colonne
[, ...] ) ] ASrequête
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
CREATE OR REPLACE VIEW
est une extension
PostgreSQL, tout comme le concept de vue temporaire.
La clause WITH ( ... )
est aussi une extension.