ストアドプロシージャ - Hologres - Alibaba Cloud ドキュメントセンター

ストアドプロシージャとは、プリコンパイルされた SQL 文のセットであり、データベースに保存して繰り返し呼び出すことができます。このトピックでは、Hologres でストアドプロシージャを使用する方法について説明します。

制限事項

Hologres V3.0 以降では、PL/pgSQL 構文を使用するストアドプロシージャがサポートされています。 PL/pgSQL 構文の詳細については、「SQL プロシージャ言語」をご参照ください。
Hologres ストアドプロシージャでは、1 つのトランザクションで複数のデータ定義言語 (DDL) 文、または 1 つのトランザクションで複数のデータ操作言語 (DML) 文を実行できます。同じトランザクションで DDL 文と DML 文を実行することはできません。詳細については、「SQL トランザクション機能」をご参照ください。
ストアドプロシージャでは、戻り値を設定できません。そのため、ストアドプロシージャをユーザー定義関数 (UDF) として使用することはできません。

権限

CREATE PROCEDURE 文を実行するには、CREATE TABLE 文を実行するために必要な権限と同じ、データベースに対する CREATE 権限が必要です。詳細については、「CREATE PROCEDURE」をご参照ください。
CREATE OR REPLACE PROCEDURE 文を実行するには、データベースに対する CREATE 権限と、置き換えるストアドプロシージャの所有権が必要です。詳細については、「CREATE PROCEDURE」をご参照ください。
ストアドプロシージャを呼び出すには、ストアドプロシージャに対する EXECUTE 権限が必要です。詳細については、「CALL」をご参照ください。

構文

Hologres ストアドプロシージャは、PostgreSQL と互換性があります。このセクションでは、Hologres ストアドプロシージャの構文について説明します。

ストアドプロシージャを作成する

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

パラメーター	説明
procedure_name	ストアドプロシージャの名前。
argname	パラメーターの名前。このパラメーターは、ストアドプロシージャの設計に基づいてオプションです。
argtype	パラメーターの型。
definition	ストアドプロシージャの実装を定義する SQL 文またはコードブロック。

パラメーターの詳細については、「CREATE PROCEDURE」をご参照ください。

ストアドプロシージャを変更する

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

パラメーター	説明
new_owner	新しいユーザー名。
CURRENT_USER	現在のユーザー名。
SESSION_USER	セッションユーザー名。

パラメーターの詳細については、「ALTER PROCEDURE」をご参照ください。

ストアドプロシージャを削除する

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

パラメーターの詳細については、「DROP PROCEDURE」をご参照ください。

ストアドプロシージャを呼び出す

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

パラメーター	説明
argument	ストアドプロシージャに必要なパラメーター。このパラメーターは、ストアドプロシージャの設計に基づいてオプションです。

パラメーターの詳細については、「CALL」をご参照ください。

例

例 1: トランザクションに複数の DDL 文を含むストアドプロシージャを作成して呼び出す。
1. ストアドプロシージャを作成する。
```
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; 
$$;
```
2. ストアドプロシージャを呼び出す。テーブル a1 と a2 は作成されますが、テーブル a3 と a4 は作成されません。
```
CALL procedure_1();
```

例 2: トランザクションに複数の DML 文を含むストアドプロシージャを作成して呼び出す。

ストアドプロシージャを作成する。

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;
$$;

ストアドプロシージャを呼び出す。

procedure_2 を呼び出す。ロールバック操作がサポートされており、データは書き込まれません。

-- DML トランザクション機能を有効にします。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを呼び出します。
CALL procedure_2();

procedure_3 を呼び出す。データが書き込まれます。

-- DML トランザクション機能を有効にします。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを呼び出します。
CALL procedure_3();

例 3: DDL 文と DML 文を含むストアドプロシージャを作成して呼び出す。

DDL 文と DML 文が個別にコミットされるストアドプロシージャを作成する。 Hologres では、同じトランザクションで DDL 文と DML 文を実行することはできません。

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;
$$;

ストアドプロシージャを呼び出す。テーブルの作成とデータの書き込みは成功します。

-- DML トランザクション機能を有効にします。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを呼び出します。
CALL procedure_4();

例 4: 入力パラメーター、中間変数、ループ、IF 条件、例外を定義するための句など、一般的な句を含むストアドプロシージャを作成して呼び出す。

ストアドプロシージャを作成する。

CREATE OR REPLACE PROCEDURE procedure_5(input text)
LANGUAGE 'plpgsql'
AS $$
-- 中間変数を定義します。
DECLARE
sql1 text;
BEGIN
    -- 入力パラメーターのテーブルにデータの行を挿入します。
    EXECUTE 'insert into ' || input || ' values(1);';
    COMMIT;

    -- a3 という名前のテーブルを作成します。
    CREATE TABLE a3(key int);
    COMMIT;

    -- 中間変数を使用して、データレコードを a3 テーブルに書き込みます。
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- FOR ループを定義します。
    FOR i IN 1..10 LOOP
        BEGIN
            -- i=1 は既にテーブルに存在するため、1 つのレコードがログに記録されます。
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'データは既に存在します。';
            -- 他の値はテーブルに存在しません。セッションは値をテーブルに書き込もうとし、RAISE EXCEPTION を使用して例外をスローします。次に、コミット操作が実行されます。
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- レポートされた例外がログに記録されます。
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'エラーをキャッチしました。';
        END;
    END LOOP;

END;
$$;

ストアドプロシージャを呼び出す。値 1 のみが a3 テーブルに書き込まれ、関連するログが記録されます。

-- DML トランザクション機能を有効にします。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを呼び出します。
CALL procedure_5('a1');

ストアドプロシージャを管理する

作成されたストアドプロシージャをクエリする。

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;

ストアドプロシージャの定義をクエリする。
```
SELECT pg_get_functiondef('<procedure_name>'::regproc);
```

トラブルシューティング

Hologres は分散システムです。そのため、Hologres インスタンスのフロントエンド (FE) ノードは分散方式でデプロイされます。 FE ノードのメタデータはリアルタイムで同期する必要があります。テーブルに対して DDL 操作が実行されたが、メタデータが同期されていない場合、DDL 操作は失敗する可能性があります。この場合、自動再試行機能がサポートされている場合、Hologres は DDL 操作を自動的に再試行します。手動操作は必要ありません。ただし、ストアドプロシージャでは、自動再試行機能がサポートされていないため、「HG_PLPGSQL_NEED_RETRY」というエラーメッセージが返されます。

ストアドプロシージャで DDL 操作が頻繁に実行されるテーブルに対して、再試行ロジックを手動で定義することをお勧めします。これにより、エラーメッセージが頻繁に報告されるのを防ぐことができます。サンプルコード:

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- セッションは DDL 文を実行しようとし、実行が成功するとループを終了します。
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- エラーメッセージ「HG_PLPGSQL_NEED_RETRY」が報告された場合、セッションはエラーをログに記録し、操作を自動的に再試行します。
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL の再試行が必要です。';
        END;
    END LOOP;
END;
$$;