Sous-ensembles de commandes SQL pris en charge dans Aurora DSQL

Cette section de PostgreSQL fournit des informations détaillées sur les expressions prises en charge, en se concentrant sur les commandes comportant des ensembles de paramètres et des sous-commandes étendus. Par exemple, CREATE TABLE dans PostgreSQL propose de nombreuses clauses et de nombreux paramètres. Cette section décrit tous les éléments de syntaxe PostgreSQL pris en charge par Aurora DSQL pour ces commandes.

CREATE TABLE

CREATE TABLE définit une nouvelle table.

CREATE TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression )|
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |

and like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | ALL }

index_parameters in UNIQUE, and PRIMARY KEY constraints are:
[ INCLUDE ( column_name [, ... ] ) ]

ALTER TABLE

ALTER TABLE modifie la définition d’une table.

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type 
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

CREATE VIEW

CREATE VIEW définit une nouvelle vue persistante. Aurora DSQL ne prend pas en charge les vues temporaires ; seules les vues permanentes sont prises en charge.

Syntaxe prise en charge

CREATE [ OR REPLACE ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]    

Description

CREATE VIEW définit une vue d’une requête. La vue n’est pas matérialisée physiquement. Au lieu de cela, la requête est exécutée chaque fois que la vue est référencée dans une requête.

CREATE or REPLACE VIEW est similaire, 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 générées par la requête de vue existante (c’est-à-dire les mêmes noms de colonnes dans le même ordre et avec les mêmes types de données), mais elle peut ajouter des colonnes supplémentaires à la fin de la liste. Les calculs à l’origine des colonnes de sortie peuvent être différents.

Si un nom de schéma est fourni (tel que CREATE VIEW myschema.myview ...), la vue est créée dans le schéma spécifié. Sinon, elle est créée dans le schéma en cours.

Le nom de la vue doit être distinct du nom de toute autre relation (table, index, vue) dans le même schéma.

Paramètres

CREATE VIEW prend en charge divers paramètres de contrôle du comportement des vues pouvant être mises à jour automatiquement.

RECURSIVE

Crée une vue récursive. La syntaxe : CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...; est équivalente à CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;.

Une liste de noms de colonnes de vue doit être spécifiée pour une vue récursive.

name

Le nom de la vue à créer, qui peut éventuellement être qualifié de schéma. Une liste de noms de colonnes doit être spécifiée pour une vue récursive.

column_name

Liste facultative des noms à utiliser pour les colonnes de la vue. Si elle n’est pas fournie, les noms de colonnes sont déduits de la requête.

WITH ( view_option_name [= view_option_value] [, ... ] )

Cette clause spécifie des paramètres facultatifs pour une vue ; les paramètres suivants sont pris en charge.

• check_option (enum) : ce paramètre peut être local ou cascaded et équivaut à la spécification WITH [ CASCADED | LOCAL ] CHECK OPTION.

• security_barrier (boolean) : cela doit être utilisé si la vue est destinée à fournir une sécurité au niveau des lignes. Aurora DSQL ne prend actuellement pas en charge la sécurité au niveau des lignes, mais cette option obligera tout de même à évaluer d’abord les conditions WHERE de la vue (et toutes les conditions utilisant des opérateurs marqués comme LEAKPROOF).

• security_invoker (boolean) : cette option permet de vérifier les relations de base sous-jacentes en fonction des privilèges de l’utilisateur de la vue plutôt que du propriétaire de la vue. Pour plus de détails, consultez les notes ci-dessous.

Toutes les options ci-dessus peuvent être modifiées sur les vues existantes à l’aide de ALTER VIEW.

query

Une commande SELECT ou VALUES qui fournira les colonnes et les lignes de la vue.

• WITH [ CASCADED | LOCAL ] CHECK OPTION : cette option contrôle le comportement des vues qui peuvent être mises à jour automatiquement. Lorsque cette option est spécifiée, les commandes INSERT et UPDATE de la vue sont vérifiées pour s’assurer que les nouvelles lignes répondent à la condition définissant la vue (c’est-à-dire que les nouvelles lignes sont vérifiées pour s’assurer qu’elles sont visibles dans la vue). Dans le cas contraire, la mise à jour sera rejetée. Si l’option CHECK OPTION n’est pas spécifiée, les commandes INSERT et UPDATE de la vue sont autorisées à créer des lignes qui ne sont pas visibles dans la vue. Les options de contrôle suivantes sont prises en charge.

• LOCAL : les nouvelles lignes ne sont vérifiées que par rapport aux conditions définies directement dans la vue elle-même. Les conditions définies sur les vues de base sous-jacentes ne sont pas vérifiées (sauf si elles spécifient également CHECK OPTION).

• CASCADED : les nouvelles lignes sont vérifiées en fonction des conditions de la vue et de toutes les vues de base sous-jacentes. Si l’option CHECK OPTION est spécifiée et que ni LOCAL ni CASCADED n’est spécifié, alors CASCADED est supposé.

Note

L’option CHECK OPTION ne peut pas être utilisée avec les vues RECURSIVE. L’option CHECK OPTION est uniquement pris en charge que sur les vues qui peuvent être mises à jour automatiquement.

Remarques

Utilisez l’instruction DROP VIEW pour supprimer des vues.

Les noms et les types de données des colonnes de la vue doivent être soigneusement étudiés. Par exemple, CREATE VIEW vista AS SELECT « Hello World; » n’est pas recommandé, car le nom de colonne par défaut est ?column?;. De plus, le type de données de colonne par défaut est text, ce qui n’est peut-être pas ce que vous vouliez.

Une meilleure approche consiste à spécifier explicitement le nom de la colonne et le type de données, tels que : CREATE VIEW vista AS SELECT text 'Hello World' AS hello;.

Par défaut, l’accès aux relations de base sous-jacentes référencées dans la vue est déterminé par les autorisations du propriétaire de la vue. Dans certains cas, cela peut être utilisé pour fournir un accès sécurisé, mais restreint aux tables sous-jacentes. Cependant, toutes les vues ne sont pas protégées contre la falsification.

• Si la propriété security_invoker de la vue est définie sur true, l’accès aux relations de base sous-jacentes est déterminé par les autorisations de l’utilisateur qui exécute la requête plutôt que par celles de la vue. Ainsi, l’utilisateur d’une vue d’invocation de sécurité doit disposer des autorisations appropriées sur la vue et ses relations de base sous-jacentes.

• Si l’une des relations de base sous-jacentes est une vue d’invocation de sécurité, elle sera traitée comme si elle avait été accessible directement depuis la requête d’origine. Ainsi, une vue d’invocation de sécurité vérifiera toujours ses relations de base sous-jacentes à l’aide des autorisations de l’utilisateur actuel, même si elle est accessible depuis une vue dépourvue de la propriété security_invoker.

• Les fonctions appelées dans la vue sont traitées de la même manière que si elles avaient été appelées directement depuis la requête utilisant la vue. Par conséquent, l’utilisateur d’une vue doit être autorisé à appeler toutes les fonctions utilisées par la vue. Les fonctions de la vue sont exécutées avec les privilèges de l’utilisateur exécutant la requête ou du propriétaire de la fonction, selon que les fonctions sont définies comme SECURITY INVOKER ou SECURITY DEFINER. Par exemple, appeler directement CURRENT_USER dans une vue renverra toujours l’utilisateur appelant, et non le propriétaire de la vue. Ceci n’est pas affecté par le paramètre security_invoker de la vue, de sorte qu’une vue dont le paramètre security_invoker défini sur false n’est pas équivalente à une fonction SECURITY DEFINER.

• L’utilisateur qui crée ou remplace une vue doit disposer de privilèges USAGE sur tous les schémas auxquels il est fait référence dans la requête de la vue, afin de pouvoir rechercher les objets référencés dans ces schémas. Notez toutefois que cette recherche n’a lieu que lorsque la vue est créée ou remplacée. Par conséquent, l’utilisateur de la vue n’a besoin du privilège USAGE que sur le schéma contenant la vue, et non sur les schémas auxquels il est fait référence dans la requête de la vue, même pour une vue d’invocation de sécurité.

• Lorsque CREATE OR REPLACE VIEW est utilisé sur une vue existante, seule la règle SELECT de la vue, ainsi que les paramètres WITH ( ... ) et son option CHECK OPTION sont modifiés. Les autres propriétés de la vue, notamment la propriété, les autorisations et les règles autres que SELECT, restent inchangées. Vous devez être propriétaire de la vue pour la remplacer (cela inclut le fait d’être membre du rôle propriétaire).

Vues qui peuvent être mises à jour

Les vues simples peuvent être mises à jour automatiquement : le système autorisera l’utilisation des instructions INSERT, UPDATE et DELETE sur la vue de la même manière que sur une table normale. Une vue peut être automatiquement mise à jour si elle répond à toutes les conditions suivantes :

• La vue doit comporter exactement une entrée dans sa liste FROM, qui doit être une table ou une autre vue pouvant être mise à jour.

• La définition de la vue ne doit pas contenir de clauses WITH, DISTINCT, GROUP BY, HAVING, LIMIT ni OFFSET au niveau supérieur.

• La définition de la vue ne doit pas contenir d’opération définies (UNION, INTERSECT ni EXCEPT) au niveau supérieur.

• La liste de sélection de la vue ne doit pas contenir d’agrégats, de fonctions de fenêtrage ni de fonctions à renvoi d’ensemble.

Une vue pouvant être automatiquement mise à jour peut contenir un mélange de colonnes pouvant et ne pouvant pas être mises à jour. Une colonne peut être mise à jour s’il s’agit d’une simple référence à une colonne pouvant être mise à jour de la relation de base sous-jacente. Dans le cas contraire, la colonne est en lecture seule et une erreur se produit si une instruction INSERT ou UPDATE tente de lui attribuer une valeur.

Pour les vues pouvant être automatiquement mises à jour, le système convertit n’importe quelle instruction INSERT, UPDATE ou DELETE de la vue en instruction correspondante sur la relation de base sous-jacente. Les instructions INSERT comportant une clause ON CONFLICT UPDATE sont entièrement prises en charge.

Si une vue pouvant être mise à jour automatiquement contient une condition WHERE, celle-ci limite les lignes de la relation de base qui peuvent être modifiées par les instructions UPDATE et DELETE figurant sur la vue. Cependant, une commande UPDATE peut modifier une ligne afin qu’elle ne réponde plus à la condition WHERE, la rendant invisible dans la vue. De même, une commande INSERT peut potentiellement insérer des lignes de relation de base qui ne satisfont pas à la condition WHERE, les rendant ainsi invisibles dans la vue. ON CONFLICT UPDATE peut également affecter une ligne existante qui n’est pas visible dans la vue.

Vous pouvez utiliser CHECK OPTION pour empêcher les commandes INSERT et UPDATE de créer des lignes invisibles dans la vue.

Si une vue pouvant être mise à jour automatiquement est marquée par la propriété security_barrier, toutes les conditions WHERE de la vue (et toutes les conditions utilisant des opérateurs marqués comme LEAKPROOF) sont toujours évaluées avant toutes les conditions ajoutées par un utilisateur de la vue. Notez que de ce fait, les lignes qui ne sont finalement pas renvoyées (parce qu’elles ne répondent pas aux conditions WHERE de l’utilisateur) peuvent tout de même être verrouillées. Vous pouvez utiliser EXPLAIN pour voir quelles conditions sont appliquées au niveau de la relation (et donc ne pas verrouiller les lignes) et lesquelles ne le sont pas.

Une vue plus complexe qui ne répond pas à toutes ces conditions est en lecture seule par défaut : le système n’autorise pas l’insertion, la mise à jour ni la suppression de la vue.

Note

L’utilisateur qui effectue l’insertion, la mise à jour ou la suppression de la vue doit disposer du privilège d’insertion, de mise à jour ou de suppression de la vue correspondant. Par défaut, le propriétaire de la vue doit avoir les privilèges nécessaires sur les relations de base sous-jacentes, tandis que l’utilisateur qui effectue la mise à jour n’a besoin d’aucune autorisation sur les relations de base sous-jacentes. Toutefois, si security_invoker est défini sur true pour la vue, c’est l’utilisateur qui effectue la mise à jour, plutôt que le propriétaire de la vue, qui doit disposer des privilèges appropriés sur les relations de base sous-jacentes.

Exemples

Création d’une vue comprenant tous les films comiques.

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

Cela créera une vue contenant les colonnes présentes dans la table film au moment de la création de la vue. Bien que * ait été utilisé pour créer la vue, les colonnes ajoutées ultérieurement à la table ne feront pas partie de la vue.

Créer une vue avec LOCAL CHECK OPTION.

CREATE VIEW pg_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'PG'
    WITH CASCADED CHECK OPTION;

Cela permet de créer une vue qui vérifie à la fois le kind et la classification des nouvelles lignes.

Créer une vue avec un mélange de colonnes pouvant ou non être mises à jour.

CREATE VIEW comedies AS
    SELECT f.*,
           country_code_to_name(f.country_code) AS country,
           (SELECT avg(r.rating)
            FROM user_ratings r
            WHERE r.film_id = f.id) AS avg_rating
    FROM films f
    WHERE f.kind = 'Comedy';

Cette vue prend en charge INSERT, UPDATE et DELETE. Toutes les colonnes de la table des films pourront être mises à jour, tandis que les colonnes country et avg_rating calculées seront en lecture seule.

CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;

Note

Bien que le nom de la vue récursive soit qualifié selon le schéma dans CREATE, son référence circulaire interne n’est pas qualifiée selon le schéma. Cela est dû au fait que le nom de l’expression de table commune (CTE) créée implicitement ne peut pas être qualifié selon le schéma.

Compatibilité

CREATE OR REPLACE VIEW est une extension du langage PostgreSQL. La clause WITH ( ... ) est également une extension, tout comme les vues des barrières de sécurité et les vues d’invocation de sécurité. Aurora DSQL prend en charge ces extensions de langage.

ALTER VIEW

L’instruction ALTER VIEW permet de modifier diverses propriétés d’une vue existante, et Aurora DSQL prend en charge l’ensemble de la syntaxe PostgreSQL pour cette commande.

Syntaxe prise en charge

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

Description

ALTER VIEW modifie diverses propriétés auxiliaires d’une vue. (Si vous souhaitez modifier la requête de définition de la vue, utilisez CREATE OR REPLACE VIEW.) Vous devez être propriétaire de la vue pour pouvoir utiliser ALTER VIEW. Pour modifier le schéma d’une vue, vous devez également disposer du privilège CREATE sur le nouveau schéma. Pour modifier le propriétaire, vous devez être en mesure de SET ROLE sur le nouveau rôle propriétaire, et ce rôle doit disposer du privilège CREATE sur le schéma de la vue. Ces restrictions font en sorte que la modification du propriétaire ne fasse rien que vous ne puissiez faire en supprimant et en recréant la vue.

Paramètres

ALTER VIEW paramètres

name

Nom (éventuellement qualifié selon le schéma) d’une vue existante.

column_name

Nouveau nom d’une colonne existante.

IF EXISTS

Ne génère pas d’erreur si la vue n’existe pas. Un avis est émis dans ce cas.

SET/DROP DEFAULT

Ces formulaires définissent ou suppriment la valeur par défaut d’une colonne. La valeur par défaut d’une colonne de vue est remplacée dans toute commande INSERT ou UPDATE dont la cible est la vue. La valeur par défaut de la vue aura priorité sur toutes les valeurs par défaut des relations sous-jacentes.

nouveau_propriétaire

Nom d’utilisateur du nouveau propriétaire de la vue.

nouveau_nom

Nouveau nom de la vue.

new_schema

Nouveau schéma de la vue.

SET ( view_option_name [= view_option_value] [, ... ] )

RESET ( view_option_name [, ... ] )

Définit ou réinitialise une option de vue. Les options suivantes sont prises en charge.

• check_option (enum) : modifie l’option de vérification de la vue. La valeur doit être local ou cascaded.

• security_barrier (boolean) : modifie la propriété de barrière de sécurité de la vue. La valeur doit être une valeur booléenne, telle que true ou false.

• security_invoker (boolean) : modifie la propriété de barrière de sécurité de la vue. La valeur doit être une valeur booléenne, telle que true ou false.

Remarques

Pour des raisons historiques liées à PostgreSQL, ALTER TABLE peut également être utilisé avec des vues ; mais les seules variantes de ALTER TABLE autorisées avec les vues sont équivalentes à celles présentées précédemment.

Exemples

Renommer la vue foo en bar.

ALTER VIEW foo RENAME TO bar;

Associer une valeur de colonne par défaut à une vue pouvant être mise à jour.

CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1);  -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2);  -- ts will receive the current time
    

Compatibilité

ALTER VIEW est une extension PostgreSQL de la norme SQL prise en charge par Aurora DSQL.

DROP VIEW

L’instruction DROP VIEW supprime une vue existante. Aurora DSQL prend en charge la syntaxe PostgreSQL complète pour cette commande.

Syntaxe prise en charge

DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Description

DROP VIEW supprime une vue existante. Pour exécuter cette commande, vous devez être propriétaire de la vue.

Paramètres

IF EXISTS

Ne génère pas d’erreur si la vue n’existe pas. Un avis est émis dans ce cas.

name

Nom (éventuellement qualifié selon le schéma) d’une vue à supprimer.

CASCADE

Supprime automatiquement les objets qui dépendent de la vue (comme d’autres vues), puis tous les objets qui dépendent de ces objets.

RESTRICT

Refuser de supprimer la vue si des objets en dépendent. Il s’agit de l’option par défaut.

Exemples

DROP VIEW kinds;          

Compatibilité

Cette commande est conforme à la norme SQL, sauf que la norme n’autorise la suppression que d’une seule vue par commande, et à part l’option IF EXISTS, qui est une extension PostgreSQL prise en charge par Aurora DSQL.