CREATE PROCEDURE

Crée une nouvelle procédure stockée ou remplace une procédure existante pour la base de données actuelle.

Pour plus d’informations et d’exemples, consultez Création de procédures stockées dans Amazon Redshift.

Privilèges requis

Vous devez être autorisé par l'une des méthodes suivantes pour exécuter la procédure CREATE OR REPLACE :

• Pour CREATE PROCEDURE :

  • Superuser

  • Utilisateurs disposant du privilège CREATE [OR REPLACE] PROCEDURE

• Pour REPLACE PROCEDURE :

  • Superuser

  • Utilisateurs disposant du privilège CREATE [OR REPLACE] PROCEDURE

  • Propriétaire de la procédure

Syntaxe

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

Paramètres

OR REPLACE

Clause qui spécifie que si une procédure ayant le même nom et les mêmes types de données, ou signature, existe déjà, la procédure existante est remplacée. Vous pouvez uniquement remplacer une procédure par une nouvelle procédure qui définit un ensemble identique de types de données.

Si vous définissez une procédure avec le même nom qu’une procédure existante, mais une signature différente, vous créez une nouvelle procédure. Autrement dit, le nom de la procédure est surchargé. Pour plus d'informations, consultez Surcharge des noms de procédure.

sp_procedure_name

Nom de la procédure. Si vous spécifiez un nom de schéma (tel que myschema.myprocedure), la procédure est créée à dans le schéma spécifié. Sinon, la procédure est créée dans le schéma en cours. Pour plus d’informations sur les noms valides, consultez Noms et identificateurs.

Nous vous conseillons d’utiliser le préfixe sp_ pour tous les noms des procédures stockées. Amazon Redshift réserve le préfixe sp_ pour les noms de procédures stockées. Utiliser le préfixe sp_ vous permet de garantir que le nom de votre procédure stockée n’est en conflit avec aucun nom de fonction ou de procédure stockée intégré à Amazon Redshift existant ou futur. Pour plus d'informations, consultez Dénomination des procédures stockées.

Vous pouvez définir plusieurs procédures portant le même nom si les types de données des arguments en entrée, ou signature, sont différents. Autrement dit, dans ce cas, le nom de la procédure est surchargé. Pour plus d'informations, consultez Surcharge des noms de procédure

[argname] [ argmode] argtype

Liste de noms d’arguments, de modes d’argument et de types de données. Seul le type de données est requis. Le nom et le mode sont facultatifs, et leur position peut être inversée.

Le mode de l’argument peut être IN, OUT ou INOUT. La valeur par défaut est IN.

Vous pouvez utiliser des arguments OUT et INOUT pour renvoyer une ou plusieurs valeurs à partir d’un appel de procédure. S’il existe des arguments OUT ou INOUT, l’appel de procédure renvoie une ligne de résultats contenant n colonnes, où n est le nombre total d’arguments OUT ou INOUT.

Les arguments INOUT sont des arguments à la fois en entrée et en sortie. Les arguments en entrée comprennent les arguments IN et INOUT, et les arguments en sortie comprennent les arguments OUT et INOUT.

Les arguments OUT ne sont pas spécifiés dans le cadre de l’instruction CALL. Spécifiez les arguments INOUT dans l’instruction CALL de la procédure stockée. Les arguments INOUT peuvent s’avérer utiles lorsque vous transmettez ou renvoyez des valeurs à partir d’appels imbriqués, ainsi que lors du renvoi d’un type refcursor. Pour plus d’informations sur les types refcursor, consultez Curseurs.

Les types de données d’arguments peuvent être de n’importe quel type de données Amazon Redshift standard. En outre, refcursor peut être un type de données d’argument.

Vous pouvez spécifier un maximum de 32 arguments en entrée et de 32 arguments en sortie.

AS $$ procedure_body $$

Construction qui englobe la procédure à exécuter. Les mots-clés littéraux AS $$ et $$ sont obligatoires.

Amazon Redshift requiert que vous placiez l’instruction dans votre procédure à l’aide d’un format appelé guillemets dollar. Tout ce qui se trouve dans l’encadrement est passé exactement comme tel. Vous n’avez pas besoin de définir de séquence d’échappement pour les caractères spéciaux, car les contenus de la chaîne sont écrits littéralement.

Avec les guillemets dollar, vous utilisez une paire de symboles dollar ($$) pour marquer le début et la fin de l’instruction à exécuter, comme illustré dans l’exemple suivant.

$$ my statement $$

Le cas échéant, entre les symboles dollar de chaque paire, vous pouvez spécifier une chaîne pour aider à identifier l’instruction. La chaîne que vous utilisez doit être la même au début et à la fin des paires d’encadrement. Cette chaîne est sensible à la casse et suit les mêmes contraintes qu’un identificateur sans guillemets, sauf que celui-ci ne peut pas contenir de symboles dollar. L’exemple suivant utilise la chaîne test.

$test$ my statement $test$

Cette syntaxe est également utile les guillemets dollar imbriqués. Pour plus d’informations sur les guillemets dollar, consultez relative aux constantes de chaîne avec guillemet dollar dans Lexical Structure dans la documentation PostgreSQL.

procedure_body

Ensemble d’instructions PL/pgSQL valides. Les instructions PL/pgSQL complémentent les commandes SQL avec des constructions procédurales, y compris des expressions de boucle et conditionnelles, pour contrôler le flux logique. La plupart des commandes SQL peuvent être utilisées dans le corps de la procédure, y compris celles du langage de modification de données (DML) telles que COPY, UNLOAD et INSERT, et celles du langage de définition de données (DDL) telles que CREATE TABLE. Pour plus d'informations, consultez Guide de référence du langage PL/pgSQL.

LANGUAGE plpgsql

Valeur pour le langage. Spécifiez plpgsql. Vous devez avoir l’autorisation pour USAGE ON LANGUAGE pour utiliser plpgsql. Pour plus d’informations, consultez GRANT.

NONATOMIC

Crée la procédure stockée dans un mode de transaction non atomique. Le mode NONATOMIC valide automatiquement les déclarations à l’intérieur de la procédure. En outre, lorsqu’une erreur se produit à l’intérieur de la procédure NONATOMIC, l’erreur n’est pas relancée si elle est gérée par un bloc d’exception. Pour plus d’informations, consultez Gestion des transactions et RAISE.

Lorsque vous définissez une procédure stockée comme NONATOMIC, tenez compte des éléments suivants :

• Lorsque vous imbriquez des appels de procédures stockées, toutes les procédures doivent être créées dans le même mode de transaction.

• L’option SECURITY DEFINER et l’option SET configuration_parameter ne sont pas prises en charge lors de la création d’une procédure en mode NONATOMIC.

• Tout curseur ouvert (explicitement ou implicitement) est fermé automatiquement lorsqu’une validation implicite est effectuée. Par conséquent, vous devez ouvrir une transaction explicite avant de commencer une boucle de curseur pour vous assurer que tout code SQL dans l’itération de la boucle n’est pas implicitement validé.

SECURITY INVOKER | SECURITY DEFINER

L’option SECURITY DEFINER n’est pas prise en charge lorsque NONATOMIC est spécifié.

Le mode de sécurité pour la procédure détermine les privilèges d’accès de la procédure lors de l’exécution. La procédure doit être autorisée à accéder aux objets de la base de données sous-jacente.

Pour le mode SECURITY INVOKER, la procédure utilise les privilèges de l’utilisateur appelant la procédure. L’utilisateur doit disposer d’autorisations explicites sur les objets de la base de données sous-jacente. La valeur par défaut est SECURITY INVOKER.

Pour le mode SECURITY DEFINER, la procédure utilise les privilèges du propriétaire de la procédure. Le propriétaire de la procédure est défini comme l’utilisateur propriétaire de la procédure au moment de l’exécution, pas nécessairement l’utilisateur qui a initialement défini la procédure. L’utilisateur qui appelle la procédure doit détenir un privilège EXECUTE sur la procédure, mais il n’a pas besoin de privilèges sur les objets sous-jacents.

SET configuration_parameter { TO value | = value }

Ces options ne sont pas prises en charge lorsque NONATOMIC est spécifié.

La clause SET entraîne la définition du configuration_parameter spécifié sur la valeur spécifiée au lancement de la procédure. Cette clause restaure ensuite configuration_parameter à sa valeur précédente à la fin de la procédure.

Notes d’utilisation

Si une procédure stockée a été créée à l’aide de l’option SECURITY DEFINER, lors de l’appel de la fonction CURRENT_USER depuis la procédure stockée, Amazon Redshift renvoie le nom d’utilisateur du propriétaire de la procédure stockée.

Exemples

Note

Si vous rencontrez une erreur similaire à ce qui suit lors de l’exécution de ces exemples :

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

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

L’exemple suivant crée une procédure avec deux paramètres d’entrée.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;

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 de sécurité, car les valeurs codées en dur peuvent être enregistrées sous forme de 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 PROCÉDURE DE CRÉATION et Création de procédures stockées dans Amazon Redshift. Pour plus d’informations sur les tables catalogue, consultez Tables de catalogue système.

L’exemple suivant crée une procédure avec un paramètre IN, un paramètre OUT et un paramètre INOUT.

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;