Référence du langage de script pgScript

Sommaire

Aperçu

pgScript est composé de commandes pgScript :

commande pgScript
    : commande standard SQL PostgreSQL (SELECT INSERT CREATE ...)
    | déclaration ou affectation de variable (DECLARE SET)
    | structure de contrôle (IF WHILE)
    | procédure (ASSERT PRINT LOG RMLINE)

Les noms de commandes (SELECT, IF, SET, ...) ne sont pas sensibles à la casse et doivent se terminer avec un point-virgule (;). Les identifiants sont sensibles à la casse.

Exemples

Créer des tables par batch

DECLARE @I, @T; -- Les noms de variables commencent avec un @
SET @I = 0; -- @I est un entier
WHILE @I < 20
BEGIN
    SET @T = 'table' + CAST (@I AS STRING); -- Convertie @I
    CREATE TABLE @T (id integer primary key, data text);

    SET @I = @I + 1;
END

Insérer des données aléatoires

DECLARE @I, @J, @T, @G;
SET @I = 0;
SET @G1 = INTEGER(10, 29, 1); /* Générateur aléatoire d'entiers
                             nombres uniques entre 10 et 29 */
SET @G2 = STRING(10, 20, 3); /* Générateur aléatoire de chaînes de caractères
                             3 mots entre 10 et 20 caractères */
WHILE @I < 20
BEGIN
    SET @T = 'table' + CAST (@I AS STRING);

    SET @J = 0;
    WHILE @J < 20
    BEGIN
        INSERT INTO @T VALUES (@G1, '@G2');
        SET @J = @J + 1;
    END

    SET @I = @I + 1;
END

Supprimer des tables par batch

DECLARE @I, @T; -- Déclaration optionnelle
SET @I = 0;
WHILE 1 -- Toujours true
BEGIN
    IF @I >= 20
      BREAK; -- Quitte la boucle si @I > 20

    SET @T = 'table' + CAST (@I AS STRING);
    DROP TABLE @T;

    SET @I = @I + 1;
END

Affiches des informations à l'écran

SET @PROGR@M#TITLE = 'pgScript';
PRINT '';
PRINT @PROGR@M#TITLE + ' features:';
PRINT '';
PRINT '  * Regular PostgreSQL commands';
PRINT '  * Control-of-flow language';
PRINT '  * Local variables';
PRINT '  * Random data generators';

Commandes SQL

Vous pouvez exécuter toute requête PostgreSQL dans un script pgScript sauf celles-ci :

BEGIN;
END;

La cause en est que BEGIN et END sont déjà utilisés pour délimiter les blocs. À la place, utilisez :

BEGIN TRANSACTION;
END TRANSACTION;

Pour une liste des commandes PostgreSQL, voir: http://docs.postgresql.fr/8.3/sql-commands.html.

Variables

Il existe deux types de variables : les variables simples et les enregistrements (ensembles de résultats composés de lignes et de colonnes).

Les noms des variables commencent avec un @ et peuvent être composés de lettres, de chiffres et des caractères _, #, @.

Le type de variable est deviné automatiquement suivant le type de la valeur qu'il contient. Voici le choix possible : nombre (real ou integer), chaîne (string), enregistrement (record).

Variables simples

Déclaration de variable simple

Déclarer une variable simple est optionnel.

DECLARE @A, @B;
DECLARE @VAR1;
Affectation de variable simple

Cela se fait avec la commande SET. Le type de variable dépend de la valeur affectée à cette variable.

SET @A = 1000, @B = 2000;   -- @A et @B sont des nombres entiers
SET @C = 10e1, @D = 1.5;    -- @C et @D sont des nombres flottants
SET @E = 'ab', @F = 'a''b'; -- @E et @F sont des chaînes de caractères
SET @G = "ab", @H = "a\"b"; -- @G et @H sont des chaînes de caractères

Une variable non initialisée est par défaut une chaîne vide. Il est possible de surcharger les variables autant de fois que nécessaire.

PRINT @A;      -- Affiche une chaîne vide
SET @A = 1000; -- @A est initialisé en tant qu'entier
PRINT @A;      -- Affiche 1000
SET @A = 'ab'; -- @A devient une chaîne
PRINT @A;      -- Affiche ab
Générateurs de données

Les générateurs de données permettent aux utilisateurs de générer des valeurs aléatoires. Il existe différents types de générateurs, chacune produisant un type différent de données. Une variable initialisée avec un générateur de données se comporte comme une variable simple normale sauf qu'elle a une valeur différente à chaque fois qu'elle est utilisée.

SET @A = INTEGER(100, 200);
PRINT @A; -- Affiche un entier entre 100 et 200
PRINT @A; -- Affiche un autre entier entre 100 et 200

Une variable peut contenir un générateur, mais son type sera soit un nombre (real ou integer) soit une chaîne. Pour une liste des générateurs disponibles et leur type associé, voir Générateur de données aléatoires.

Enregistrements

Déclaration d'un enregistrement

Déclarer un enregistrement est requis. Un nom pour chaque colonne doit être spécifié même si elles ne seront pas utilisées après.

DECLARE @R1 { @A, @B }, @R2 { @A, @C }; -- Deux enregistrements avec deux colonnes
DECLARE @R3 { @A, @B, @C, @D };         -- Un enregistrement avec quatre colonnes

Le nombre de lignes est dynamiques : voir la prochaine section.

Affectation de l'enregistrement

Pour accéder à un emplacement spécifique d'un enregistrement, vous pouvez utiliser le numéro de ligne (commence à 0) et peut utiliser soit le nom de la colonne (entre guillemets) ou le numéro de la colonne (commence à 0). Cette emplacement spécifique se comporte comme une variable simple. Notez qu'un enregistrement ne peut pas contenir un enregistrement.

SET @R1[0]['@A'] = 1; -- Première ligne & première colonne
SET @R1[0][0] = 1;    -- Même emplacement
SET @R1[4]['@B'] = 1; -- Cinquième ligne & deuxième colonne
SET @R1[0][1] = 1;    -- Même emplacement

Dans l'exemple ci-dessus, les trois lignes lignes sont automatiquement insérées entre la première et la cinquième. Utiliser un nombre ou un nom de colonne invalide cause une exception.

Un emplacement spécifique peut être utilisé comme une valeur valide. Une ligne spécifique peut aussi être utilisée comme valeur.

SET @R1[0][0] = @R3[0][1], @A = @R2[0][0]; -- Se comporte commes des variables simples
SET @A = @R1[1]; -- @A devient un enregistrement qui est la première ligne de @R1

Souvenez-vous que SET @R1[0][0] = @R2 est impossible car un enregistrement ne peut pas contenir un enregistrement.

Il est possible d'affecter un enregistrement dans une variable. Dans ce cas, la variable n'a pas besoin d'être déclarée :

SET @A = @R3; -- @A devient un enregistrement car il se voit affecter un enregistrement
Requêtes SQL

Toute requête SQL exécutée renvoie un enregistrement. Si la requête est un SELECT, alors elle renvoie les résultats de la requête. Si c'est quelque-chose d'autre, alors elle renvoie un enregistrement d'une ligne (true) si c'est un succès, et un enregistrement sans ligne dans le cas contraire (false).

SET @A = SELECT * FROM table;   -- @A est un enregistrement avec les résultats de la requête
SET @B = INSERT INTO table ...; -- @B est un enregistrement d'une ligne si la requête réussit
Fonctions des enregistrements

Voir Fonctions.

Conversion

Il est possible de convertir une variable d'un type vers un autre avec la fonction de conversion :

SET @A = CAST (@B AS STRING);
SET @A = CAST (@B AS REAL);
SET @A = CAST (@B AS INTEGER);
SET @A = CAST (@B AS RECORD);

Quand un enregistrement est converti en une chaîne de caractères, il est converti dans sa représentation plate. Quand il est converti en un nombre, l'enregistrement est tout d'abord converti en une chaîne, puis en un nombre (voir la conversion des chaînes pour plus de détails).

Quand un nombre est converti en chaîne, il est converti en utilisant sa représentation de chaîne. Quand il est converti en un enregistrement, il est converti en un enregistrement d'une ligne et une colonne dont la valeur est le nombre.

Quand une chaîne est convertie en un nombre, si la chaîne représente un nombre, alors ce dernier est renvoyé, sinon une exception est levée. Quand elle est convertie en un enregistrement, soit le programme peut trouver un modèle d'enregistrement dans la chaîne soit il est converti en un enregistrement d'une ligne et une colonne dont la valeur est la chaîne. Un modèle d'enregistrement est :

SET @B = '(1, "abc", "ab\\"")(1, "abc", "ab\\"")'; -- @B est une chaîne
SET @B = CAST (@B AS RECORD); @B devient un enregistrement de deux lignes et de trois colonnes

Rappelez-vous qu'une chaîne est entourée par des guillemets simples. Les chaînes composant un enregistrement doit être entouré par des guillemets doubles qui sont échappés avec \\ (nous doublons l'antislash car il est déjà un caractère spécial pour les guillemets simples).

Opérations

Les opérations peuvent seulement être réalisées entre des opérandes de même type. Convertissez les valeurs pour vous conformer à cette règle.

Le résultat des comparaisons est un nombre 0 ou 1.

Chaînes

Comparaisons : = <> > < <= >= AND OR

Concaténation : +

SET @B = @A + 'abcdef'; -- @A et @B doivent être une chaîne

Valeur booléenne : une chaîne non vide est true, une chaîne vide est false

Inverser une valeur booléenne : NOT

Comparaison sans prendre en compte la casse : ~=

Nombres

Comparaisons : = <> > < <= >= AND OR

Arithmétique : + - * / %

SET @A = CAST ('10' AS INTEGER) + 5; -- La chaîne '10' est convertie en un nombre

Valeur booléenne : 0 est false, tout le reste est true

Inverser une valeur booléenne: NOT (notez que NOT NOT 10 = 1)

Une opération arithmétique impliquant au moins un nombre flottant donne un nombre flottant en résultat :

SET @A = 10 / 4.; -- 4. est un nombre flottant, donc un résultat en nombre flottant: @A = 2.5
SET @A = 10 / 4;  -- 4 est un entier, donc un résultat en entier: @A = 2
Enregistrements

Comparaisons : = <> > < <= >= AND OR

Valeur booléenne: un enregistrement de zéro ligne vaut false, tout le reste vaut true

Inverser une valeur booléenne : NOT

Comparaisons pour les enregistrements sur l'inclusion et l'exclusion. L'ordre des lignes n'importe pas. <= signifie que chaque ligne dans l'opérande gauche a une correspondance dans l'opérande de droite. >= signifie l'inverse. = signifie que <= et >= sont tous les deux true...

Les comparaisons sont réalisées sur les chaînes : même si un enregistrement contient des nombres comme 10 et 1e1, nous aurons '10' <> '1e1'.

Structures de contrôle

Structure conditionnelle

IF condition
BEGIN
    commandes pgScript
END
ELSE
BEGIN
    commandes pgScript
END

Les commandes pgScript sont optionnelles. Les mots clés BEGIN et END sont optionnels s'il y a une seule commande pgScript.

Structure de boucle

WHILE condition
BEGIN
    commandes pgScript
END

Les commandes pgScript sont optionnelles. Les mots clés BEGIN et END sont optionnels s'il y a une seule commande pgScript.

BREAK termine la boucle WHILE alors que CONTINUE provoque la prochaine itération de la boucle en cours d'exécution. RETURN se comporte comme BREAK.

WHILE condition1
BEGIN
    IF condition2
    BEGIN
        BREAK;
    END
END

Conditions

Les conditions sont en fait les résultats d'opérations. Par exemple, la comparaison de chaînes 'ab' = 'ac' résulte en un nombre qui est false (l'égalité n'est pas vraie).

IF 'ab' ~= 'AB' -- Comparaison sans attention à la casse qui donne pour résultat 1 (true)
BEGIN
    -- On entre ici
END

IF 0 -- faux
BEGIN
    -- On n'arrive pas ici
END
ELSE
BEGIN
    -- On entre ici
END

WHILE 1
BEGIN
    -- Boucle infinie : utiliser BREAK pour sortir
END

Il est possible d'utiliser le résultat d'une requête SQL SELECT comme condition. La requête a besoin d'être entourée avec des parenthèses :

IF (SELECT 1 FROM table)
BEGIN
    -- Ceci signifie que la table existe, sinon la condition serait fausse
END

Fonctions et procédures supplémentaires

Procédures

Les procédures ne renvoient pas de résultat. Elles doivent être utilisées seules sur une ligne et ne peuvent pas être affectées à une variable.

Print

Affiche une expression sur l'écran :

PRINT 'La valeur de @A est ' + CAST (@A AS STRING);
Assert

Renvoie une exception si l'expression évaluée est false :

ASSERT 5 > 3 AND 'a' = 'a';
Remove line

Supprime la ligne spécifiée d'un enregistrement :

RMLINE(@R[1]); -- Supprime la deuxième ligne de @R

Fonctions

Les fonctions renvoient un résultat. Leur code de retour peut être affecté à une variable, comme l'opération CAST.

Trim

Supprime les espaces devant et derrière une chaîne de caractères :

SET @A = TRIM(' a '); -- @A = 'a'
Lines

Renvoie le nombre de lignes d'un enregistrement :

IF LINES(@R) > 0
BEGIN
    -- Traitement
END
Columns

Renvoie le nombre de colonnes d'un enregistrement :

IF COLUMNS(@R) > 0
BEGIN
    -- Traitement
END

Générateurs aléatoires de données

Aperçu des générateurs

Un utilisateur peut affecter une variable (SET) avec un génération de données. Cela signifie que chaque fois que la variable sera utilisée, elle aura une valeur différente.

Néanmoins, la variable est toujours utilisée comme d'habitude :

SET @G = STRING(10, 20, 2);
SET @A = @G; -- @A contiendra une chaîne au hasard
SET @B = @G; -- @B contiendra une autre chaîne au hasard
PRINT @G,    -- affichera une troisième autre chaîne

Séquence et graine

Les paramètres communs pour les générateurs de données sont séquence et seed.

séquence signifie qu'une séquence de valeurs est générée en aléatoire. En d'autres termes, chaque valeur apparaît seulement une fois avant le début de la séquence : ceci est utile pour les colonnes avec une contrainte UNIQUE. Par exemple, ce générateur :

SET @G = INTEGER(10, 15, 1); -- 1 signifie la génération d'une séquence

Il peut générer ce type de valeurs : 14 12 10 13 11 15 14 12 10 13 11... où chaque nombre apparaît une fois avant que la séquence recommence.

Le paramètre séquence doit être un entier : s'il vaut 0, alors aucune séquence n'est générée (par défaut), sinon une séquence est générée.

graine est une valeur entière pour initialiser un générateur : deux générateurs avec les mêmes paramètres et la même graine généreront exactement les mêmes valeurs.

graine doit être un entier : il est utilisé directement pour initialiser le générateur aléatoire de données.

Générateurs de données

Les paramètres optionnels sont placés entre crochets.

Generator
    : INTEGER ( min, max, [sequence], [seed] );
    | REAL ( min, max, precision, [sequence], [seed] );
    | DATE ( min, max, [sequence], [seed] );
    | TIME ( min, max, [sequence], [seed] );
    | DATETIME ( min, max, [sequence], [seed] );
    | STRING ( min, max, [nb], [seed] );
    | REGEX ( regex, [seed] );
    | FILE ( path, [sequence], [seed], [encoding] );
    | REFERENCE ( table, column, [sequence], [seed] );
Nombres entier
INTEGER ( min, max, [sequence], [seed] );
INTEGER ( -10, 10, 1, 123456 );

min est un entier, max est un entier, sequence est un entier et seed est un entier.

Nombre flottants
REAL ( min, max, precision, [sequence], [seed] );
REAL ( 1.5, 1.8, 2, 1 );

min est un nombre, max est un nombre, precision est un entier qui indique le nombre de décimales (doit être inférieur à 30), sequence est un entier et seed est un entier.

Dates
DATE ( min, max, [sequence], [seed] );
DATE ( '2008-05-01', '2008-05-05', 0 );

min est une chaîne représentant une date, max est une chaîne représentant une date, sequence est un entier et seed est un entier.

Heures
TIME ( min, max, [sequence], [seed] );
TIME ( '00:30:00', '00:30:15', 0 );

min est une chaîne représentant une heure, max est une chaîne représentant une heure, sequence est un entier et seed est un entier.

Horodatages (date/times)
DATETIME ( min, max, [sequence], [seed] );
DATETIME ( '2008-05-01 14:00:00', '2008-05-05 15:00:00', 1 );

min est une chaîne représentant un horodatage, max est une chaîne représentant un horodatage, sequence est un entier et seed est un entier.

Chaînes de caractères
STRING ( min, max, [nb], [seed] );
STRING ( 10, 20, 5 );

min est un entier représentant la longueur minimale d'un mot, max est un entier représentant la longueur maximale d'un mot, nb est un entier représentant le nombre de mots (par défaut : 1) et seed est un entier.

Dans l'exemple ci-dessus, nous générons cinq mots (séparés avec un espace) dont la taille est entre dix et vingt caractères.

Chaînes des expressions rationnelles
REGEX ( regex, [seed] );
REGEX ( '[a-z]{1,3}@[0-9]{3}' );

regex est une chaîne représentant une expression rationnelle simplifiée et seed est un entier.

Les expressions rationnelles simplifiées sont composées de :

Il est possible de spécifier la longueur minimale et maximale de l'ensemble précédent ou de simple caractère :

Note : faites attention aux espaces car 'a {3}' signifie un a suivi par trois espaces car le 3 concerne le dernier caractère ou ensemble de caractère qu'il suit.

Si vous avez besoin d'utiliser [ ] \ { ou }, ils doivent être échappés car ce sont des caractères spéciaux. Rappelez-vous d'utiliser un double antislash : '\\[{3}' pour trois [.

Chaînes de fichiers dictionnaires
FILE ( path, [sequence], [seed], [encoding] );
FILE ( 'file.txt', 0, 54321, 'utf-8' );

path est une chaîne représentant le chemin d'un fichier texte, sequence est un entier, seed est un entier et encoding est une chaîne représentant le jeu de caractères du fichier (par défaut, il s'agit de l'encodage du système).

Ceci génère un entier aléatoire entre 1 et le nombre de lignes du fichier, puis renvoie cette ligne. Si le fichier n'existe pas, une exception est levée.

encoding supporte les encodages les plus connus comme utf-8, utf-16le, utf-16be, iso-8859-1, ...

Référence à un autre champ
REFERENCE ( table, column, [sequence], [seed] );
REFERENCE ( 'tab', 'col', 1 );

table est une chaîne représentant une table, column est une chaîne représentant une colonne de la table, sequence est un entier et seed est un entier.

Ceci est utile pour générer des données à placer dans les colonnes contraintes avec une clé étrangère.