Hologres でのストアドプロシージャの作成と実行 - Hologres - Alibaba Cloud - Hologres

制限事項

Hologres は V3.0 以降で PL/pgSQL 構文を使用するストアドプロシージャをサポートします。PL/pgSQL 構文の詳細については、「SQL Procedural Language」をご参照ください。
Hologres のストアドプロシージャでは、1 つのトランザクション内で複数の DDL ステートメント、または 1 つのトランザクション内で複数の DML ステートメントを実行できます。ただし、同一トランザクション内で DDL ステートメントと DML ステートメントを混在させることはできません。詳細については、「Transactions」をご参照ください。
ストアドプロシージャは戻り値をサポートしておらず、ユーザー定義関数 (UDF) として使用することはできません。
ストアドプロシージャは、結果セットを返すための RETURN QUERY ステートメントをサポートしていません。データを返すには、ビューまたは一時テーブルを使用します。
ストアドプロシージャは CURSOR 操作をサポートしていません。
ストアドプロシージャ内で別のストアドプロシージャを定義することはできません。
ストアドプロシージャは、動的 SQL の結果を変数に代入するための EXECUTE ... INTO ステートメントをサポートしていません。代わりに SELECT ... INTO ステートメントを使用します。

権限

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

コマンドリファレンス

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

ストアドプロシージャの作成

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 ステートメントの両方を含むストアドプロシージャ
1. ストアドプロシージャを作成します。Hologres は同一トランザクション内で DDL ステートメントと DML ステートメントを混在させることをサポートしていないため、ストアドプロシージャ内で 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;
$$;
```
2. ストアドプロシージャを呼び出します。テーブルが作成され、データは正常に書き込まれます。
```
-- DML トランザクション機能を有効化します。
SET hg_experimental_enable_transaction = ON;

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

例 4：入力パラメーターの定義、中間変数、ループ、IF 条件、EXCEPTION 処理などの一般的な機能を示すストアドプロシージャ

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

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

    -- テーブル a3 を作成します。
    CREATE TABLE a3(key int);
    COMMIT;

    -- 中間変数を使用して、テーブル a3 に 1 行のデータを挿入します。
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- FOR ループを定義します。
    FOR i IN 1..10 LOOP
        BEGIN
            -- i=1 はすでにテーブルに存在するため、notice のみが発生します。
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'Data already exists.';
            -- その他の数値はテーブルに存在しません。システムは挿入を試行し、
            -- EXCEPTION を発生させます。
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- 発生した EXCEPTION に対して notice を出力します。
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'Catch error.';
        END;
    END LOOP;

END;
$$;

ストアドプロシージャを呼び出します。値 1 はテーブル a3 に書き込まれますが、それ以外のデータは書き込まれず、関連する notice がすべて出力されます。
```
-- DML トランザクション機能を有効化します。
SET hg_experimental_enable_transaction = ON;

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

例 5：CASE WHEN 式を使用して変数を動的に算出し、再利用する

宛先テーブルとストアドプロシージャを作成します。このプロシージャは、DECLARE で変数を宣言し、CASE WHEN 式で時間パラメーターを動的に算出し、複数の INSERT ステートメントで変数を再利用します。

-- 宛先テーブルを作成します。
CREATE TABLE test_dynamic_param (
    event_name TEXT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

-- CASE WHEN 式を使用して時間パラメーターを動的に算出するストアドプロシージャを作成します。
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
    -- CASE WHEN を使用して 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;

    -- 複数の INSERT ステートメントで変数を再利用します。
    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;
$$;

ストアドプロシージャを呼び出して結果を確認します。2 つのレコードの end_time 値は、同一の CASE WHEN 計算から導出されるため同一になります。

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

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

-- 検証：2 つのレコードの end_time 値は同一です。
SELECT * FROM test_dynamic_param;

ストアドプロシージャの管理

ストアドプロシージャを表示します。

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 は分散システムであり、DDL 操作中にフロントエンドノード (FE) 間でメタデータをリアルタイムに同期する必要があります。メタデータの同期が完了していない場合、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 エラーが発生した場合は notice を出力し、操作を再試行します。
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;