CREATE VIEW — Définir une vue
CREATE USER MAPPING
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW nom [ ( nom_colonne [, ...] ) ]
[ WITH ( nom_option_vue [= valeur_option_vue] [, ... ] ) ]
AS requê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.
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).
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 RECURSIVE nom (colonnes) AS (SELECT ...) SELECT colonne FROM nom;
Une liste de noms de colonne doit être spécifiée pour la vue récursive.
Le nom de la vue à créer (éventuellement qualifié du nom du schéma).
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.
Cette clause spécifie des paramètres optionnels pour une vue. Les paramètres supportés sont les suivants :
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(7).
Ceci doit être utilisé si la vue a pour but de fournir une sécurité au niveau ligne. Voir Section 39.5, « Règles et droits » pour plus de détails.
Une commande SELECT(7) ou VALUES(7) qui fournira les colonnes et lignes de la vue.
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 :
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).
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(7) 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';
présente deux défauts majeurs : le nom de la colonne prend la valeur implicite ?column? et son type de données le type implicite unknown. Pour obtenir une chaîne de caractères dans le résultat de la vue, on peut écrire :
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 39.5, « Règles et droits » 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éfintion 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 39.5, « Règles et droits » 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(7). Une autre possibilité revient à créer des règles (voir CREATE RULE(7)). 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 39.5, « Règles et droits »).
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 VIEW nom [ ( nom_colonne [, ...] ) ]
AS requê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.