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

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


#dblinkとは

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

[Let's POSTGRES PostgreSQL情報ポータルサイト](https://lets.postgresql.jp/documents/technical/contrib/dblink)から引用

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

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

##事前準備

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

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

##dblinkでプロシージャ（引数なし）を実行
```sql:
PERFORM DBLINK_EXEC('データベース接続名', 'CALL プロシージャ名');
```

###サンプルコード
```sql: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パラメータあり）を実行

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

```

###サンプルコード

```sql: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でキャッチすることは可能
　

#参考
https://www.postgresql.jp/document/12/html/contrib-dblink-function.html