Stored procedures - Hologres - Centre de documentation Alibaba Cloud

Limitations

Hologres prend en charge les procédures stockées utilisant la syntaxe PL/pgSQL à partir de la version 3.0. Pour plus d'informations sur la syntaxe PL/pgSQL, consultez la documentation Langage procédural SQL.
Dans les procédures stockées Hologres, vous pouvez exécuter plusieurs instructions DDL au sein d'une même transaction ou plusieurs instructions DML au sein d'une même transaction. En revanche, il est impossible d'exécuter des instructions DDL et DML dans la même transaction. Pour plus de détails, reportez-vous à la rubrique Transactions.
Les procédures stockées ne renvoient pas de valeurs et ne peuvent pas être utilisées comme fonctions définies par l'utilisateur (UDF).
Les procédures stockées ne prennent pas en charge l'instruction RETURN QUERY pour renvoyer des ensembles de résultats. Pour retourner des données, utilisez une vue ou une table temporaire.
Les procédures stockées ne prennent pas en charge l'opération CURSOR.
Vous ne pouvez pas définir une procédure stockée à l'intérieur d'une autre procédure stockée.
Les procédures stockées ne prennent pas en charge l'instruction EXECUTE ... INTO pour assigner les résultats d'un SQL dynamique à une variable. Utilisez plutôt l'instruction SELECT ... INTO.

Autorisations

Pour exécuter l'instruction CREATE PROCEDURE, vous devez disposer de l'autorisation CREATE sur la base de données. Il s'agit de la même autorisation requise pour créer une table. Pour plus d'informations, consultez la documentation CREATE PROCEDURE.
Pour exécuter l'instruction CREATE OR REPLACE, vous devez disposer de l'autorisation CREATE sur la base de données et être le propriétaire de la procédure stockée que vous souhaitez remplacer. Pour plus d'informations, consultez la documentation CREATE PROCEDURE.
Pour appeler une procédure stockée, vous devez disposer de l'autorisation EXECUTE sur celle-ci. Pour plus d'informations, consultez la documentation CALL.

Référence des commandes

Hologres prend en charge une syntaxe de procédure stockée compatible avec PostgreSQL. La section suivante décrit cette syntaxe.

Créer une procédure stockée

CREATE [ OR REPLACE ] PROCEDURE
    <procedure_name> ([<argname> <argtype>])
LANGUAGE 'plpgsql'
AS <definition>;

Paramètre	Description
procedure_name	Nom de la procédure stockée.
argname	Nom d'un argument. Ce paramètre est facultatif et dépend de la conception de la procédure stockée.
argtype	Type de données de l'argument.
definition	Implémentation spécifique de la procédure stockée. Il peut s'agir d'une instruction SQL ou d'un bloc de code.

Pour plus d'informations sur les paramètres, consultez la documentation CREATE PROCEDURE.

Modifier une procédure stockée

ALTER PROCEDURE <procedure_name> ([<argname> <argtype>])
    OWNER TO <new_owner> | CURRENT_USER | SESSION_USER;

Paramètre	Description
new_owner	Nouveau propriétaire.
CURRENT_USER	Utilisateur actuel.
SESSION_USER	Utilisateur de la session.

Pour plus d'informations sur les paramètres, consultez la documentation ALTER PROCEDURE.

Supprimer une procédure stockée

DROP PROCEDURE [ IF EXISTS ] <procedure_name> ([<argname> <argtype>]);

Pour plus d'informations sur les paramètres, consultez la documentation DROP PROCEDURE.

Appeler une procédure stockée

CALL <procedure_name> ([<argument>]);

Paramètre	Description
argument	Argument de la procédure stockée. Ce paramètre est facultatif et dépend de la conception de la procédure.

Pour plus d'informations sur les paramètres, consultez la documentation CALL.

Exemples

Exemple 1 : Procédure stockée avec une transaction DDL multi-instructions

Créez une procédure stockée.

CREATE OR REPLACE PROCEDURE procedure_1()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    --- TXN1 --- 
    CREATE TABLE a1(key int);
    CREATE TABLE a2(key int);
    COMMIT; 

    --- TXN2 ---
    CREATE TABLE a3(key int);
    CREATE TABLE a4(key int);
    ROLLBACK;
END; 
$$;

Appelez la procédure stockée. Les tables a1 et a2 sont créées, mais les tables a3 et a4 ne le sont pas.
```
CALL procedure_1();
```

Exemple 2 : Procédure stockée avec une transaction DML multi-instructions

Créez les procédures stockées.

CREATE OR REPLACE PROCEDURE procedure_2()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
    ROLLBACK;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure_3()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
END;
$$;

Appelez les procédures stockées.

Appelez procedure_2. La transaction est annulée (rollback) et aucune donnée n'est écrite.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_2();

Appelez procedure_3. Les données sont écrites avec succès.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_3();

Exemple 3 : Procédure stockée contenant à la fois des instructions DDL et DML
1. Créez une procédure stockée. Comme Hologres ne prend pas en charge le mélange d'instructions DDL et DML dans la même transaction, vous devez valider (commit) les opérations DDL et DML séparément au sein de la procédure stockée.
```
CREATE OR REPLACE PROCEDURE procedure_4()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    COMMIT;	
    CREATE TABLE bb(key int);
    COMMIT;	
    INSERT INTO a1 VALUES(2);
    INSERT INTO bb VALUES(1);    
    COMMIT;	
END;
$$;
```
2. Appelez la procédure stockée. La table est créée et les données sont écrites avec succès.
```
-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_4();
```

Exemple 4 : Une procédure stockée illustrant des fonctionnalités courantes, telles que la définition de paramètres d'entrée, de variables intermédiaires, de boucles, de conditions IF et la gestion des EXCEPTIONS.

Créez une procédure stockée.

CREATE OR REPLACE PROCEDURE procedure_5(input text)
LANGUAGE 'plpgsql'
AS $$
-- Define an intermediate variable.
DECLARE
sql1 text;
BEGIN
    -- Insert a row of data into the table specified by the input parameter.
    EXECUTE 'insert into ' || input || ' values(1);';
    COMMIT;

    -- Create table a3.
    CREATE TABLE a3(key int);
    COMMIT;

    -- Use the intermediate variable to insert a row into table a3.
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- Define a FOR loop.
    FOR i IN 1..10 LOOP
        BEGIN
            -- Because i=1 already exists in the table, only a notice is raised.
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'Data already exists.';
            -- Other numbers do not exist in the table. The system attempts to insert them,
            -- raises an EXCEPTION, and then commits.
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- For the raised EXCEPTION, raise a notice.
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'Catch error.';
        END;
    END LOOP;

END;
$$;

Appelez la procédure stockée. La valeur 1 est écrite dans la table a3, aucune autre donnée n'est écrite et tous les avis associés sont imprimés.
```
-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_5('a1');
```

Exemple 5 : Utilisation d'une expression CASE WHEN pour calculer dynamiquement et réutiliser une variable.

Créez la table de destination et la procédure stockée. La procédure déclare une variable avec DECLARE, calcule dynamiquement un paramètre de temps avec une expression CASE WHEN et réutilise la variable dans plusieurs instructions INSERT.

-- Create the destination table.
CREATE TABLE test_dynamic_param (
    event_name TEXT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

-- Create a stored procedure that uses a CASE WHEN expression to dynamically calculate a time parameter.
CREATE OR REPLACE PROCEDURE procedure_dyn_time()
LANGUAGE 'plpgsql'
AS $$
DECLARE
    new_end_time TIMESTAMPTZ;
    base_start_time TIMESTAMPTZ := '2024-01-01 00:00:00+08'::TIMESTAMPTZ;
BEGIN
    -- Use CASE WHEN to dynamically calculate end_time.
    new_end_time := CASE
        WHEN NOW() > base_start_time + INTERVAL '5 min'
        THEN base_start_time + INTERVAL '1 hour'
        ELSE base_start_time + INTERVAL '5 min'
    END;

    -- Reuse the variable in multiple INSERT statements.
    INSERT INTO test_dynamic_param VALUES('event1', base_start_time, new_end_time);
    INSERT INTO test_dynamic_param VALUES('event2', base_start_time, new_end_time);
END;
$$;

Appelez la procédure stockée et vérifiez le résultat. Les valeurs end_time des deux enregistrements sont identiques car elles proviennent du même calcul CASE WHEN.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_dyn_time();

-- Verification: The end_time values for the two records are identical.
SELECT * FROM test_dynamic_param;

Gérer les procédures stockées

Consultez les procédures stockées.

SELECT
    p.proname AS procedure_name,
    pg_get_function_identity_arguments(p.oid) AS argument_types,
    REPLACE(pg_get_functiondef(p.oid),'$procedure$','$$') AS procedure_detail,
    n.nspname AS schema_name,
    r.rolname AS owner_name,
    d.description AS description
FROM
    pg_proc p
    INNER JOIN pg_namespace n ON p.pronamespace = n.oid
    INNER JOIN pg_roles r ON p.proowner = r.oid
    LEFT JOIN pg_description d ON p.oid = d.objoid
WHERE
    r.rolname != 'holo_admin'
    AND p.prokind = 'p'
ORDER BY
    n.nspname,
    p.proname;

Consultez la définition d'une procédure stockée.

SELECT pg_get_functiondef('<procedure_name>'::regproc);

FAQ

Hologres est un système distribué qui doit synchroniser les métadonnées entre ses nœuds frontend (FE) en temps réel lors des opérations DDL. Si la synchronisation des métadonnées n'est pas terminée, l'opération DDL peut échouer. Bien que Hologres retente généralement automatiquement les opérations DDL ayant échoué, ce mécanisme n'est pas pris en charge au sein des procédures stockées. Si ce problème survient dans une procédure stockée, le système renvoie une erreur HG_PLPGSQL_NEED_RETRY.

Pour éviter les erreurs sur les tables subissant des modifications DDL fréquentes, implémentez une logique de nouvelle tentative manuelle dans vos procédures stockées. Le code suivant fournit un exemple :

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- Try to run the DDL statement. If the statement is successful, exit the loop.
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- If an HG_PLPGSQL_NEED_RETRY error occurs, print a notice and retry the operation.
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;