昨日は ↓ の簡単なコードで、PostgreSQL上でアドホックにRを実行するストアド関数を試しました。今日はもう少し実用的に、PostgreSQLに入っているデータを渡す方法について。実行環境は昨日と同様Windows 7 32bit + PostgreSQL Portable 9.4 + R 3.1.2 + PL/R 8.3.0.16です。


一時的なTSV（タブ区切りテキスト）ファイルで渡す
今日試した ↓ は原始的な方法で、最初に適当なダミーデータでビューを作り、次にCOPYコマンドで一時TSVファイルに出力。ここまではPL/Rと関係ない、一般的なPostgreSQLの使用です。三つ目のブロックが、昨日作ったPL/R用の「擬似DO文」ストアドを利用する所で、Rのread.delim関数を使いTSVを読み込み。コードの下に、実行風景の画像を示します。
plr_do_example.sqlSelectRawtextBitbucket
-- create dummy data
CREATE VIEW test.dummy AS
SELECT concat('2015-05-', day) :: date AS "ymd",
       round(random() :: numeric * 100, -1) AS "foovar",
       CASE WHEN random() < 0.5 THEN 0 ELSE 1 END AS "event"
FROM generate_series(1, 31) "day";

-- save to a temporary file
COPY (SELECT * FROM test.dummy)
TO 'R:/to_plr.tsv' (DELIMITER E'\t', FORMAT csv, HEADER TRUE);

-- pseudo DO
DO $_d_$
BEGIN
PERFORM test.plr_do($_r_$
    dat = read.delim('R:/to_plr.tsv', stringsAsFactors=F)
    head(dat, 20)
$_r_$);
END $_d_$;

上の例では一時TSVの場所をRドライブとし、事前にPostgreSQLのユーザにRドライブへの読み取り・書き込み権限を与えています。COPYコマンドでの出力先指定（TOの次）は絶対パスが必要。またCOPYコマンドのオプションでFORMAT csvとあるのはtsvの間違いではなく、このオプションを入れることで、区切り文字とヘッダ有無を設定できます。（詳細はドキュメントを参照）

一時TSVの場所をCOPYとRコードで2回入力するのが難点ですが、1回で済ますには、DOの中にCOPYコマンドを入れ、ファイルパスを変数化し、COPYコマンドを動的クエリとして組み立ててEXECUTEするという回りくどい方法しか無さそうで、元々の趣旨（アドホックな手軽な実行）から離れるため不採用。

Rには、CSVやTSVなどのテキストファイルを読み込むread.table関数とその一群があり、今日使ったread.delimもその一つです。詳細は下のドキュメントを参照。オプション引数でstringsAsFactors=Fを指定しないと、数値の列も因子型になってしまうのが少し面倒。簡単にする方法がないか、今後調べます。

»R Documentation : read.table {utils}


実行例と制約
これで一応アドホックに実行するRの中へPostgreSQLデータを渡せたので、後はRの処理を適宜追加できます。下のExample 1はデータの第2・3列でクロス集計、Example 2はglm関数でロジスティック回帰。どちらの結果も、特にprint文は必要ありませんでした。
-- Example 1

DO $_d_$
BEGIN
PERFORM test.plr_do($_r_$
    dat = read.delim('R:/to_plr.tsv', stringsAsFactors=F)
    table(dat[2:3]) # contingency table
$_r_$);
END $_d_$;


-- Example 2

DO $_d_$
BEGIN
PERFORM test.plr_do($_r_$
    dat = read.delim('R:/to_plr.tsv', stringsAsFactors=F)
    options(show.signif.stars = F)
    summary(glm(dat$event ~ dat$foovar, family=binomial)) # logistic regression
$_r_$);
END $_d_$;

普通にRを使うのと違いPostgreSQLサーバ上に一時的にRを乗せているような感じなので、制約もあります。例えばhead、summaryといった何か出力する関数を複数箇所で記述しても、最後の結果しか表示されません。対策としてはRのsink関数を使う（全ての出力を一時ファイルに保存できるので、それを最後にまとめて読み込む）ことが考えられ、今後必要になったら試します。

あと試しに出力ファイル指定なしでplotを実行したら、Rのコンソールで行うのと同様、グラフィックウィンドウが立ち上がりました。が、それを手動で閉じたり移動したりできず、クエリツールで再び何か実行したら消えるという微妙な挙動。もう少し調べて別記事にします。