Structure de PL/pgSQL

PL/pgSQL est un langage procédural avec un grand nombre de constructions communes à d'autres langages procéduraux.

Bloc

PL/pgSQL est un langage structuré en blocs. Le corps complet d'une procédure est défini dans un bloc, lequel contient les déclarations de variables et les instructions PL/pgSQL. Une instruction peut également être un bloc imbriqué ou un sous-bloc.

Terminez les déclarations et les instructions par un point-virgule. Faites suivre le mot clé END d'un point-virgule dans un bloc ou un sous-bloc. N'utilisez pas de point-virgule après les mots clés DECLARE et BEGIN.

Vous pouvez écrire tous les mots clés et les identifiants en mélangeant majuscules et minuscules. Les identifiants sont implicitement convertis en minuscules à moins d'être placés entre guillemets doubles.

Un tiret double (--) marque le début d'un commentaire qui s'étend jusqu'à la fin de la ligne. Les caractères /* marquent le début d'un commentaire de bloc qui s'étend jusqu'à l'occurrence suivante de */. Vous ne pouvez pas imbriquer des commentaires de bloc. Toutefois, vous pouvez placer des commentaires avec tiret double dans un commentaire de bloc, et un tiret double peut masquer les délimiteurs de commentaire de bloc /* et */.

Toute instruction figurant dans la section des instructions d'un bloc peut être un sous-bloc. Vous pouvez utiliser des sous-blocs pour effectuer un regroupement logique ou pour localiser des variables dans un petit groupe d'instructions.

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
END [ label ];  

Les variables déclarées dans la section des déclarations précédant un bloc sont initialisées à leur valeur par défaut à chaque entrée dans le bloc. En d'autres termes, elles ne sont pas initialisées une seule fois par appel de fonction.

Vous en trouverez un exemple ci-dessous.

CREATE PROCEDURE update_value() AS $$
DECLARE
  value integer := 20;
BEGIN
  RAISE NOTICE 'Value here is %', value;  -- Value here is 20
  value := 50;
  --
  -- Create a subblock
  --
  DECLARE
    value integer := 80;
  BEGIN
    RAISE NOTICE 'Value here is %', value;  -- Value here is 80
  END;

  RAISE NOTICE 'Value here is %', value;  -- Value here is 50
END;
$$ LANGUAGE plpgsql;               

Utilisez une étiquette pour identifier le bloc à utiliser dans une instruction EXIT ou pour qualifier les noms des variables déclarées dans le bloc.

Ne confondez pas l'utilisation de BEGIN/END pour regrouper des instructions dans PL/pgSQL avec les commandes de base de données pour le contrôle des transactions. Dans PL/pgSQL, les mots clés BEGIN et END sont utilisés uniquement à des fins de regroupement. Ils ne démarrent pas et ne finissent pas une transaction.

Déclaration de variable

Déclarez toutes les variables d'un bloc, à l'exception des variables de boucle, dans la section DECLARE du bloc. Les variables peuvent utiliser un type de données Amazon Redshift quelconque valide. Pour connaître les types de données pris en charge, consultez Types de données.

Les variables PL/pgSQL peuvent être d'un type de données quelconque pris en charge par Amazon Redshift, plus RECORD et refcursor. Pour plus d’informations sur RECORD, consultez Types d'enregistrement. Pour plus d’informations sur refcursor, consultez Curseurs.

DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

Vous trouverez ci-après des exemples de déclarations de variables.

customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;

La variable de boucle d'une boucle FOR effectuant une itération sur une plage d'entiers est automatiquement déclarée en tant que variable de type entier.

La clause DEFAULT, si elle est spécifiée, fournit la valeur initiale attribuée à la variable lors de l'entrée dans le bloc. Si la clause DEFAULT n'est pas spécifiée, la variable est initialisée avec la valeur SQL NULL. L'option CONSTANT empêche l'attribution d'une valeur à la variable, afin que sa valeur reste constante pendant toute la durée du bloc. Si NOT NULL est spécifié, l'attribution d'une valeur null entraîne une erreur d'exécution. Toutes les variables déclarées comme NOT NULL doivent avoir une valeur par défaut non null spécifiée.

La valeur par défaut est évaluée à chaque entrée dans le bloc. Par exemple, l'attribution de now() à une variable de type timestamp fait que la variable contiendra l'heure de l'appel actuel de la fonction, et non pas l'heure de la précompilation de la fonction.

quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;

Le type de données refcursor est le type de données des variables de curseur au sein des procédures stockées. Une valeur refcursor peut être retournée d'une procédure stockée. Pour plus d’informations, consultez Retour d’un ensemble de résultats.

Déclaration d'alias

Si la signature d'une procédure stockée omet le nom de l'argument, vous pouvez déclarer un alias pour cet argument.

name ALIAS FOR $n;

Variables intégrées

Les variables intégrées suivantes sont prises en charge :

• FOUND

• SQLSTATE

• SQLERRM

• GET DIAGNOSTICS integer_var := ROW_COUNT;

FOUND est une variable spéciale de type booléen. FOUND commence avec la valeur false au sein de chaque appel de procédure. La variable FOUND est définie par les types d'instructions suivants :

• SELECT INTO

  Attribue à FOUND la valeur true si une ligne est renvoyée, et la valeur false si aucune ligne n'est renvoyée.

• UPDATE, INSERT et DELETE

  Attribue à FOUND la valeur true si au moins une ligne est affectée, et la valeur false si aucune ligne n'est affectée.

• FETCH

  Attribue à FOUND la valeur true si une ligne est renvoyée, et la valeur false si aucune ligne n'est renvoyée.

• Instruction FOR

  Attribue à FOUND la valeur true si l'instruction FOR effectue une ou plusieurs itérations, sinon la valeur false. Cela s'applique aux trois variantes de l'instruction FOR : les boucles FOR de type entier, les boucles FOR de type jeu d'enregistrements et les boucles FOR de type jeu d'enregistrements dynamique.

  FOUND est définie à la sortie de la boucle FOR. Pendant l'exécution de la boucle, la variable FOUND n'est pas modifiée par l'instruction FOR. Toutefois, elle peut être modifiée en exécutant d'autres instructions dans le corps de la boucle.

Vous en trouverez un exemple ci-dessous.

CREATE TABLE employee(empname varchar);
CREATE OR REPLACE PROCEDURE show_found()
AS  $$
DECLARE
  myrec record;
BEGIN
  SELECT INTO myrec * FROM employee WHERE empname = 'John';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'employee John not found';
  END IF;
END;
$$ LANGUAGE plpgsql;

Dans un gestionnaire d'exceptions, la variable spéciale SQLSTATE contient le code d'erreur correspondant à l'exception qui a été levée. La variable spéciale SQLERRM contient le message d'erreur associé à l'exception. Ces variables sont indéfinies en dehors des gestionnaires d'exception et affichent une erreur si elles sont utilisées.

Vous en trouverez un exemple ci-dessous.

CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS
$$
BEGIN
  UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
  EXECUTE 'select invalid';
  EXCEPTION WHEN OTHERS THEN
  RAISE INFO 'error message SQLERRM %', SQLERRM;
  RAISE INFO 'error message SQLSTATE %', SQLSTATE;
END;
$$ LANGUAGE plpgsql;               

ROW_COUNT est utilisée avec la commande GET DIAGNOSTICS. Elle indique le nombre de lignes traitées par la dernière commande SQL envoyée au moteur SQL.

Vous en trouverez un exemple ci-dessous.

CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;               

Types d'enregistrement

Un type RECORD n'est pas un vrai type de données, seulement un espace réservé. Les variables de type d'enregistrement assument la structure de ligne réelle de la ligne à laquelle elles sont attribuées au cours de l'exécution d'une commande SELECT ou FOR. La sous-structure d'une variable d'enregistrement peut changer chaque fois qu'une valeur lui est attribuée. Tant qu'une variable d'enregistrement n'a pas été attribuée, elle n'a aucune sous-structure. Toute tentative d'accès à un champ compris dedans génère une erreur d'exécution.

name RECORD;

Vous en trouverez un exemple ci-dessous.

CREATE TABLE tbl_record(a int, b int);
INSERT INTO tbl_record VALUES(1, 2);
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    RAISE INFO 'a = %', rec.a;
  END LOOP;
END;
$$;