PostgreSQL 関数やプロシージャを呼び出す方法 – TIBCOサポート

インフォメーションリンクからPostgreSQL 関数（function）やプロシージャ（procedure）を呼び出す方法について説明します。

概要

Spotfire製品の標準機能ではインフォメーションリンクからPostgreSQL関数（function）を呼び出すことが可能です。

また、デフォルト構成ではPostgreSQLプロシージャ（procedure）を呼び出すことはできませんが、
データソースの接続URLの後ろにJDBC接続プロパティ「escapeSyntaxCallMode=call」をつけることで対応可能になります。

ただし、１つのデータソースでPostgreSQL関数やプロシージャの両方に対応することはできません。
対応策として、関数呼び出し用のデータソースと、プロシージャ呼び出し用のデータソースを別々で作成してご使用ください。

動作確認環境

Spotfire Server 14.0 LTS　※理論上10.8.1以降なら対応可能
Spotfire Analyst 14.0 LTS
PostgreSQL 17　※11以降が必要

動作検証用データ

PostgreSQLデータベース内にある以下のテーブル（名前：balance）を用いて説明します。

カラム名	データ型
id	int4
val	varchar

上記のテーブル内に以下の１行のデータが存在します。

本例ではこの行（id=1）に対して val 列の値を動作検証用のPostgreSQL関数やプロシージャで変更します。

id	val
1	1

PostgreSQL内に以下の関数（update_balance_func、本例では戻り値を返さない）が存在します。

CREATE OR REPLACE FUNCTION public.update_balance_func(IN p_val integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
  update balance set val=p_val where id=1;
END;
$function$
;

PostgreSQL内に以下のプロシージャ（update_balance_proc）が存在します。

CREATE OR REPLACE PROCEDURE public.update_balance_proc(IN p_val integer)
LANGUAGE plpgsql
AS $procedure$
BEGIN
   update balance set val=p_val where id=1;
END;
$procedure$
;

インフォメーションデザイナーにて、以下の通りにデータソースを作成して対象PostgreSQLへ接続します。

接続URL：

jdbc:postgresql://postgresql-server:5432/dms

PostgreSQL関数（function）の呼び出し

製品標準機能ではインフォメーションリンクからPostgreSQL関数を呼び出すことができます。特別な処置は特に不要です。

１．PostgreSQL関数（本例：update_balance_func）を右クリックしてSpotfire内のプロシージャ要素を作成します。

PostgreSQL関数やプロシージャは歯車のアイコンで表示されます。

２．続いてプロシージャタイプ「Pre」を選択したうえで、パラメータの入力「p_val」に対して既定値「?val」（Spotfire内のパラメータを定義しておき、後で値を指定する）を入力して保存します。

３．プロシージャ要素は単独では動作しませんので、インフォメーションリンクに追加して利用する必要があります。対象インフォメーションリンクを右クリックして編集画面を開きます。

本例ではPostgreSQL関数やプロシージャが更新するテーブル（名前：balance）のデータを取り込むインフォメーションリンク（名前：balance_func）を使用していますので、インフォメーションリンク実行時にPostgreSQL関数やプロシージャで更新された後の値がSpotfireへ取り込まれます。

４．作成したプロシージャ要素（本例：update_balance_func）を対象のインフォメーションリンク（本例：balance_func）の要素一覧に追加します。プロシージャ要素の追加位置は任意で構いませんが、保存すると一番上へ移動されます。

インフォメーションリンク保存後、プロシージャ要素内に定義されたパラメータが自動的にインフォメーションリンクのパラメータ一覧に表示されます。必要に応じてデータ型や値のタイプを変更して構いません。

５．対象インフォメーションリンクを実行すると、パラメータ「?val」の値を指定するダイアログが表示され、値を指定した後にインフォメーションリンクおよびプロシージャ要素が実行されます。パラメータ「?val」の値はオンデマンドロードを用いて指定することも可能です（詳細は省略）。

PostgreSQLプロシージャ（procedure）の呼び出し

製品標準機能ではインフォメーションリンクからPostgreSQLプロシージャ（procedure）を呼び出すことはできません。対応方法として、以下のようにデータソースの接続URLを編集してください。

変更前：

jdbc:postgresql://postgresql-server:5432/dms

変更後：（「?escapeSyntaxCallMode=call」の部分を追記）

jdbc:postgresql://postgresql-server:5432/dms?escapeSyntaxCallMode=call

これでインフォメーションリンクからPostgreSQLプロシージャを呼び出すことができるようになります。

利用手順は前述の「PostgreSQL関数（function）の呼び出し」部分とは同じです。

PostgreSQL関数（function）とプロシージャ（procedure）の両対応

製品標準機能では、残念ながら１つのデータソースを用いてPostgreSQL関数（function）やプロシージャ（procedure）の両方に対応することはできません。２つのデータソースを別々で作成しておいて、関数（function）やプロシージャ（procedure）のそれぞれに対応するように設定してください。

既知問題

インフォメーションリンクのSQLが編集されると、プロシージャ要素内に定義されたパラメータ（本例：?val）が正しく認識されなくなります。

回避策として、SQL文内でプロシージャ要素内のパラメータと同名なパラメータ（本例：?val）を改めて定義してください。インフォメーションリンク実行時に、SQL内のパラメータやプロシージャ要素内のパラメータの両方には同じ値が渡されます。

補足

PostgreSQL 11 よりプロシージャ（procedure）が提供されています。

PostgreSQL 11.0 release notes

Major enhancements in PostgreSQL 11 include:

SQL stored procedures that support embedded transactions

JDBC接続プロパティ「escapeSyntaxCallMode」は PostgreSQL JDBC Driver 42.2.9 にて追加されています。Spotfire Server 10.8.1より、 PostgreSQL JDBC Driver 42.2.9以降のバージョンが製品に同梱されています。

PostgreSQL JDBC Driver 42.2.9 Released | pgJDBC

Added

new “escapeSyntaxCallMode” connection property PR 1560