Une séquence est en fait une table spéciale contenant une seule ligne. Cet objet est utilisé pour créer une suite de nombres entiers dont l’évolution, généralement croissante, est régie par un certain nombre de paramètres.
Voici la syntaxe de création d’une séquence :
CREATE SEQUENCE nom [ INCREMENT [ BY ] incrément ]
[ MINVALUE valeurmin ]
[ MAXVALUE valeurmax ]
[ START [ WITH ] début ]
[ [ NO ] CYCLE ]
La commande CREATE SEQUENCE crée un nouveau générateur de nombre. Ceci implique la création et l’initialisation d’une nouvelle table portant le nom nom.
Bien que vous ne pouvez pas mettre à jour directement une séquence, vous pouvez toujours utiliser une requête comme :
SELECT * FROM nom_sequence;
Après la création d’une séquence, il faut utiliser les fonctions nextval(), currval() et setval() pour la manipuler.
Vous pouvez appeler ces différentes fonctions de la manière suivante :
SELECT nextval('nom_sequence');
SELECT currval('nom_sequence');
SELECT setval('nom_sequence', nombre);
Utilisez DROP SEQUENCE pour supprimer une séquence.
Le type de donnée SERIAL n’est pas un vrai type, mais plutôt un raccourci de notation pour décrire une colonne d’identifiants uniques. Ainsi, la commande
CREATE TABLE nom_de_table (
nom_de_colonne SERIAL
);
est équivalente à la commande :
CREATE SEQUENCE nom_de_sequence;
CREATE TABLE nom_de_table (
nom_de_colonne integer DEFAULT nextval('nom_de_sequence') NOT NULL
);
Ainsi, nous avons créé une colonne d’entiers et fait en sorte que ses valeurs par défaut soient assignées par un générateur de séquence. Une contrainte NOT NULL est ajoutée pour s’assurer qu’une valeur nulle ne puisse pas être explicitement insérée. Dans la plupart des cas, on ajoute également une contrainte UNIQUE ou PRIMARY KEY pour interdire que des doublons soient créés par accident.
Pour insérer la valeur suivante de la séquence dans la colonne de type SERIAL, il faut faire en sorte d’utiliser la valeur par défaut de la colonne. Cela peut se faire de deux façons : soit en excluant cette colonne de la liste des colonnes de la commande INSERT, ou en utilisant le mot clé DEFAULT.
Le système de règles autorise la définition d’actions alternatives à réaliser sur les insertions, mises à jour ou suppressions dans les tables de la base de données. Concrètement, une règle permet d’exécuter des commandes supplémentaires lorsqu’une commande donnée est exécutée sur une table donnée. Autrement dit, une règle peut remplacer une commande donnée par une autre ou faire qu’une commande ne soit pas exécutée. Les règles sont aussi utilisées pour implémenter les vues de tables (cf. section 4.9.3).
Voici la syntaxe de création d’une règle :
CREATE [ OR REPLACE ] RULE nom AS
ON événement
TO table [ WHERE condition ]
DO [ INSTEAD ] { NOTHING | commande | ( commande ; commande ... ) }
À l’intérieur d’une condition et d’une commande, deux tables spéciales, NEW et OLD, peuvent être utilisées pour se référer à la table sur laquelle porte la règle. NEW est valide dans les règles ON INSERT et ON UPDATE pour désigner la nouvelle ligne en cours d’insertion ou de mise à jour. OLD est valide dans les règles ON UPDATE et ON DELETE pour désigner la ligne existante en cours de modification ou de suppression.
DROP RULE nom ON relation [ CASCADE | RESTRICT ]
Les vues sont des tables virtuelles qui « contiennent » le résultat d’une requête SELECT. L’un des intérêts de l’utilisation des vues vient du fait que la vue ne stocke pas les données, mais fait référence à une ou plusieurs tables d’origine à travers une requête SELECT, requête qui est exécutée chaque fois que la vue est référencée. De ce fait, toute modification de données dans les tables d’origine est immédiatement visible dans la vue dès que celle-ci est à nouveau référencée dans une requête.
Les utilisations possibles d’une vue sont multiples :
Voici la syntaxe de création d’une vue :
CREATE [ OR REPLACE ] VIEW nom [ ( nom_colonne [, ...] ) ] AS requête
La norme SQL propose un ensemble important de restrictions pour la modification ou l’insertion ou la modification des données dans les vues. Les systèmes de gestion de base de données ont aussi chacun leur implantation de ce concept et chacun leurs contraintes et restrictions. En particulier, peu d’opérations sont autorisées dès qu’une vue porte sur plusieurs tables ; aucune n’est possible si la vue comporte des opérateurs d’agrégation.
Avec PostgreSQL les vues ne sont que consultables par des instructions SELECT (i.e. lecture seule). Aucune autre opération n’est possible (insertion, mise à jour ou suppression de lignes). Par contre, la notion de règles permet, avec PostgreSQL, d’implémenter ces fonctionnalités. Cette notion s’avère plus souple et puissante que les restrictions communément appliquées aux SGBD classiques.
Avec PostgreSQL, les vues sont implémentées en utilisant le système de règles. En fait, il n’y aucune différence entre
CREATE VIEW mavue AS SELECT * FROM matable;
et ces deux commandes
CREATE TABLE mavue (liste de colonnes identique à celle de matable);
CREATE RULE "_RETURN" AS ON SELECT TO mavue DO INSTEAD
SELECT * FROM matable;
parce que c’est exactement ce que fait la commande CREATE VIEW en interne. Ainsi, pour l’analyseur, il n’y a aucune différence entre une table et une vue : il s’agit de relations.
DROP VIEW nom [, ...] [ CASCADE | RESTRICT ]
Les schémas sont des espaces dans lesquels sont référencés des éléments (tables, vues, index...). La notion de schéma est très liée à la notion d’utilisateur ou de groupe d’utilisateurs.
CREATE SCHEMA nom_schéma
CREATE SCHEMA crée un nouveau schéma dans la base de données en cours. Le nom du schéma doit être distinct du nom des différents schémas existants dans la base de données en cours.
Le paramètre nom_schéma est le nom du schéma à créer.
Lorsqu’une table nom_table est dans un schéma nom_schema, pour la désigner, il faut faire précéder son nom par le nom du schéma qui la contient de la manière suivante : nom_schema.nom_table. C’est ce que l’on appel un nom qualifié.
Les noms qualifiés sont pénibles à écrire et il est, de toute façon, préférable de ne pas coder un nom de schéma dans une application. Donc, les tables sont souvent appelées par des noms non qualifiés (i.e. nom de la table lui même). Le système détermine quelle table est appelée en suivant un chemin de recherche qui est une liste de schémas à regarder. La première table correspondante est considérée comme la table voulue. S’il n’y a pas de correspondance, une erreur est levée, même si des noms de table correspondants existent dans d’autres schémas dans la base.
Le premier schéma dans le chemin de recherche est appelé le schéma courant. En plus d’être le premier schéma parcouru, il est aussi le schéma dans lequel de nouvelles tables seront créées si la commande CREATE TABLE ne précise pas de nom de schéma.
Pour voir le chemin de recherche courant, utilisez la commande suivante :
SHOW search_path;
Pour ajouter un nouveau schéma mon_schema dans le chemin tout en conservant dans ce chemin le schéma par défaut (public), nous utilisons la commande :
SET search_path TO mon_schema,public;
La commande DROP SCHEMA permet de supprimer des schémas de la base de données. La syntaxe de la commande est la suivante :
DROP SCHEMA nom [, ...] [ CASCADE | RESTRICT ]
Un schéma peut seulement être supprimé par son propriétaire ou par un super utilisateur.