【postgreSQL】dblinkでプロシージャを実行させる方法

はじめに

postgreSQLでリモートデータベースでプロシージャを呼び出す。
そのような処理を行うために、postgreSQLのDBLINKを調べていたのですが、サンプルコードがほとんど見つからなかったので備忘録として残します。

dblinkとは

dblink を使うと、分散環境で複数のデータベースをまたがる処理を行ったり、同じサーバ内の別のデータベースを操作することができます。

Let's POSTGRES PostgreSQL情報ポータルサイトから引用

dblinkでプロシージャを実行させる方法

postgreSQLのバージョンは12を利用しています

事前準備

検証用に事前に以下のTBLを作成する。

CREATE TABLE PLPGSQL.DBLINK_TEST_TBL (
    COL1 INTEGER PRIMARY KEY,
    COL2 TEXT
);

dblinkでプロシージャ（引数なし）を実行

PERFORM DBLINK_EXEC('データベース接続名', 'CALL プロシージャ名');

サンプルコード

dblinkでプロシージャ（引数なし）

/*
 * DBLINK実行FUNCTION
 * FUNCTION内でDBLINKを行いPROCEDUREを呼び出す
 */
CREATE OR REPLACE FUNCTION PLPGSQL.DBLINK_TEST_FUNC() RETURNS VOID
AS $$
DECLARE
BEGIN
    RAISE INFO '[START] PLPGSQL.DBLINK_TEST_FUNC';

    -- dblink_connect　実行
    PERFORM DBLINK_CONNECT('dblink_test', 'host=localhost port=5432 dbname=example-db user=pg-user');
    RAISE INFO 'dblink_connect成功！！';
    -- procedure(引数なし)　実行
    PERFORM DBLINK_EXEC('dblink_test', 'CALL PLPGSQL.DBLINK_TEST_PROC()');
    RAISE INFO 'dblink_exec成功！！';
    -- dblink_disconnect　実行
    PERFORM DBLINK_DISCONNECT('dblink_test');
    RAISE INFO 'dblink_disconnect成功！！';

    RAISE INFO '[END] PLPGSQL.DBLINK_TEST_FUNC';

	RETURN;

    EXCEPTION
        WHEN OTHERS THEN
            PERFORM DBLINK_DISCONNECT('dblink_test');
            RAISE INFO 'dblink_disconnect成功！！';
            RAISE EXCEPTION '[EXCEPTION] SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
			RETURN;
END;
$$
LANGUAGE plpgsql;

/*
 * DBLINK実行FUNCTIONから呼び出されるPROCEDURE
 */
CREATE OR REPLACE PROCEDURE PLPGSQL.DBLINK_TEST_PROC() 
AS $$
DECLARE
	num NUMERIC;
BEGIN
    RAISE INFO '[START] PLPGSQL.DBLINK_TEST_PROC';
    INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (1, '1');
    INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (2, '2');
    INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (3, '3');
	INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (4, '3');

    SELECT COUNT(*) INTO num FROM PLPGSQL.DBLINK_TEST_TBL;
	RAISE INFO 'PLPGSQL.DBLINK_TEST_TBL の件数：%件', num;
    RAISE INFO '[END] PLPGSQL.DBLINK_TEST_PROC';
END;
$$
LANGUAGE plpgsql;

実行結果

example-db=# SELECT PLPGSQL.DBLINK_TEST_FUNC();
INFO:  [START] PLPGSQL.DBLINK_TEST_FUNC
INFO:  dblink_connect成功！！
INFO:  dblink_exec成功！！
INFO:  dblink_disconnect成功！！
INFO:  [END] PLPGSQL.DBLINK_TEST_FUNC
 dblink_test_func 
------------------
 
(1 row)

dblinkでプロシージャ（INOUTパラメータあり）を実行

SELECT プロシージャのINOUTパラメータ名 INTO 変数名 FROM DBLINK('データベース接続名', FORMAT('CALL プロシージャ名(%s)', 変数名)) AS t1 (プロシージャのINOUTパラメータ名 データ型);

サンプルコード

dblinkでプロシージャ（INOUTパラメータあり）

/*
 * DBLINK実行FUNCTION
 * FUNCTION内でDBLINKを行いPROCEDUREを呼び出す
 */
CREATE OR REPLACE FUNCTION PLPGSQL.DBLINK_TEST(retVal OUT NUMERIC) 
AS $$
DECLARE
    num numeric := 0; 
BEGIN
    RAISE INFO '[START] PLPGSQL.DBLINK_TEST';

    -- dblink_connect　実行
    PERFORM DBLINK_CONNECT('dblink_test', 'host=localhost port=5432 dbname=example-db user=pg-user');
    RAISE INFO 'dblink_connect成功！！';
    -- procedure(IN OUT)　実行
    SELECT pRetVal INTO num FROM DBLINK('dblink_test', FORMAT('CALL PLPGSQL.DBLINK_TEST_PROC(%s)', num)) AS t1 (pRetVal NUMERIC);
    RAISE INFO 'dblink成功！！';
    -- dblink_disconnect　実行
    PERFORM DBLINK_DISCONNECT('dblink_test');
    RAISE INFO 'dblink_disconnect成功！！';

    retVal := num;

    RAISE INFO '[END] PLPGSQL.DBLINK_TEST';

    EXCEPTION
        WHEN OTHERS THEN
            PERFORM DBLINK_DISCONNECT('dblink_test');
            RAISE INFO 'dblink_disconnect成功！！';
            RAISE EXCEPTION '[EXCEPTION] SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$
LANGUAGE plpgsql;

/*
 * DBLINK実行FUNCTIONから呼び出されるPROCEDURE
 */
CREATE OR REPLACE PROCEDURE PLPGSQL.DBLINK_TEST_PROC(pRetVal IN OUT NUMERIC) 
AS $$
DECLARE
BEGIN
    RAISE INFO '[START] PLPGSQL.DBLINK_TEST_PROC';
    INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (1, '1');
    BEGIN 
        INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (2, '2');
        INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (3, '3');
		-- 一意制約違反を発生させる
		INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (3, '3');
    EXCEPTION
        WHEN OTHERS THEN
            RAISE INFO '[EXCEPTION] SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
    END;

    INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (4, '1');
    COMMIT;

    INSERT INTO PLPGSQL.DBLINK_TEST_TBL (COL1, COL2) VALUES (5, '1');

    ROLLBACK;

    SELECT COUNT(*) INTO pRetVal FROM PLPGSQL.DBLINK_TEST_TBL;

    RAISE INFO '[END] PLPGSQL.DBLINK_TEST_PROC';
END;
$$
LANGUAGE plpgsql;

実行結果

example-db=# SELECT PLPGSQL.DBLINK_TEST();
INFO:  [START] PLPGSQL.DBLINK_TEST
INFO:  dblink_connect成功！！
INFO:  dblink成功！！
INFO:  dblink_disconnect成功！！
INFO:  [END] PLPGSQL.DBLINK_TEST
 dblink_test 
-------------
           2
(1 row)

補足

• DBLINKでPROCEDUREを呼び出した際、PROCEDURE内のRAISR文を実行してもコンソールにメッセージが出力されない
• PL/pgSQLは本来、FUNCTION内でcommit / rollback ができない（エラーとなる）が、DBLINKで後続のPROCEDUREを呼ぶ場合はエラーとならずにcommit / rollbackが機能する（ただし、DBLINK内で再度FUNCTIONを呼び出した場合はエラーとなる）
• DBLINK内で実行した処理のエラーを呼び元のFUNCTIONでキャッチすることは可能
  　

参考