Présentation des procédures stockées dans Amazon Redshift

Les procédures stockées permettent généralement d’encapsuler la logique de transformation et de validation des données, et la logique propre à l’activité. En associant plusieurs étapes SQL dans une même procédure stockée, vous pouvez réduire les allers-retours entre vos applications et la base de données.

Pour un contrôle d’accès détaillé, vous pouvez créer des procédures stockées qui permettent d’exécuter des fonctions sans offrir à l’utilisateur un accès aux tables sous-jacentes. Par exemple, seul le propriétaire ou un superutilisateur peut tronquer une table, et un utilisateur a besoin de privilèges d’écriture pour insérer des données dans une table. Au lieu d’accorder à un utilisateur des privilèges sur les tables sous-jacentes, vous pouvez créer une procédure stockée qui exécute la tâche. Vous accordez ensuite à l’utilisateur les privilèges nécessaires à l’exécution de la procédure stockée.

Une procédure stockée dotée de l’attribut de sécurité DEFINER s’exécute avec les privilèges du propriétaire de la procédure stockée. Par défaut, une procédure stockée dispose d’une sécurité INVOKER, ce qui signifie que la procédure utilise les privilèges de l’utilisateur qui l’appelle.

Pour créer une procédure stockée, utilisez la commande CREATE PROCEDURE. Pour exécuter une procédure, utilisez la commande CALL. Vous trouverez des exemples plus loin dans la présente section.

Note

Certains clients peuvent rencontrer l’erreur suivante lors de la création d’une procédure stockée Amazon Redshift.

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

Cette erreur est due à l’incapacité du client à analyser correctement l’instruction CREATE PROCEDURE avec des points-virgules délimitant les instructions et le signe dollar ($) entre guillemets. Il en résulte qu’une partie seulement de l’instruction est envoyée au serveur Amazon Redshift. Généralement, vous pouvez contourner cette erreur à l’aide de l’option Run as batch ou Execute selected du client.

Par exemple, avec un client Aginity, utilisez l’option Run entire script as batch. Lorsque vous utilisez SQL Workbench/J, nous vous recommandons la version 124. Lorsque vous utilisez SQL Workbench/J version 125, envisagez de spécifier un autre délimiteur comme solution de contournement.

CREATE PROCEDURE contient des instructions SQL délimitées par un point-virgule (;). La définition d’un autre délimiteur tel que la barre oblique (/) et son positionnement à la fin de l’instruction CREATE PROCEDURE permet que l’instruction soit envoyée au serveur Amazon Redshift pour traitement. Voici un exemple.

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

Pour plus d’informations, consultez Alternate delimiter dans la documentation SQL Workbench/J. Vous pouvez également utiliser un client qui prend mieux en charge l'analyse des instructions CREATE PROCEDURE, tel que l'éditeur de requêtes de la console Amazon Redshift ou. TablePlus

Rubriques

• Dénomination des procédures stockées
• Sécurité et privilèges des procédures stockées
• Retour d’un ensemble de résultats
• Gestion des transactions
• Interception des erreurs
• Enregistrement des procédures stockées
• Considérations relatives à la prise en charge des procédures stockées

L’exemple suivant illustre une procédure sans arguments en sortie. Par défaut, les arguments sont des arguments en entrée (IN).

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL   

Note

Lorsque vous écrivez des procédures stockées, nous vous recommandons une bonne pratique pour sécuriser les valeurs sensibles :

Ne codez pas en dur des informations sensibles dans la logique des procédures stockées. Par exemple, n’attribuez pas de mot de passe utilisateur dans une instruction CREATE USER dans le corps d’une procédure stockée. Cela présente un risque pour la sécurité, car les valeurs codées en dur peuvent être enregistrées en tant que métadonnées de schéma dans les tables du catalogue. Transmettez plutôt des valeurs sensibles, telles que des mots de passe, en tant qu’arguments à la procédure stockée, au moyen de paramètres.

Pour plus d’informations sur les procédures stockées, consultez CREATE PROCEDURE et Création de procédures stockées dans Amazon Redshift. Pour plus d’informations sur les tables de catalogue, consultez Tables catalogue système.

L’exemple suivant illustre une procédure avec des arguments en sortie. Les arguments sont en entrée (IN), en entrée et en sortie (INOUT), et en sortie (OUT).

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)