R と PostgreSQL

PL/R

PostgreSQL 用 R 手続き型言語(R Procedural Language for PostgreSQL)

PL/R とは

PostgreSQL の SQL よりRの機能を呼び出す。

インストール(Linux)

  • PL/R のソースをPostgreSQL の contrib で解凍
  • make を実行してビルド
  • "make install" でライブラリをインストール
  • contrib/plr ディレクトリにある plr.sql で、任意のデータベース内で有効にする
    • psql -d mydatabase -f plr.sql

インストール(Win32)

利用方法

関数の基本形

 以下のようにPL/R形の手続きを定義する

CREATE OR REPLACE FUNCTION 関数名 (引数タイプ)
RETURNS return-type AS '
   関数本体 −− ここにRのスクリプトを書く
' LANGUAGE 'plr';
  • R スクリプトのみの例
     引き数の変数名は以下のように、arg1,arg2 ...., argn となる。
CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
   if (arg1 > arg2)
      return(arg1)
   else
      return(arg2)
' LANGUAGE 'plr' STRICT;

PostgreSQL 7.5 では、以下のように引数名が定義できる。この引数名はSQLの関数内でそのまま使える。

CREATE OR REPLACE FUNCTION sd(vals FLOAT8[]) RETURNS FLOAT AS '
   sd(vals)
' LANGUAGE 'plr' STRICT;

上のSQL文では最後に"STRICT"を入れてNULL値対策をおこなっている。

"STRICT" を使用しない場合

CREATE OR REPLACE FUNCTION r_max (INTEGER, INTEGER) RETURNS INTEGER AS '
   if (is.null(arg1) && is.null(arg2))
       return(NULL)
   if (is.null(arg1))
       return(arg2)
   if (is.null(arg2))
       return(arg1)
   if (arg1 > arg2)
       return(arg1)
   arg2
' LANGUAGE 'plr';

 複合型の戻り値を返す例(データフレームの値を返す)。

CREATE OR REPLACE FUNCTION get_emps() RETURNS SETOF emp AS '
   names <- c("Joe","Jim","Jon")
   ages <- c(41,25,35)
   salaries <- c(250000,120000,50000)
   df <- data.frame(name = names, age = ages, salary = salaries)
   return(df)
' LANGUAGE 'plr';

SELECT * FROM get_emps();

 name | age |  salary
------+-----+-----------
 Jim  |  41 | 250000.00
 Joe  |  25 | 120000.00
 Jon  |  35 |  50000.00
(3 rows)

 SQL内にRスクリプトを定義せずに、R関数と同名かつ引数の型も同じであるSQL関数を定義する場合。その際、SQL関数内はなにもない。

CREATE OR REPLACE FUNCTION sd(_float8) RETURNS FLOAT AS '' LANGUAGE 'plr';
SELECT ROUND(sd('{1.23,1.31,1.42,1.27}'::_FLOAT8)::NUMERIC,8);
   round    
------------
 0.08180261
(1 row)

大域データ

データベースアクセスサポート関数

 以下のコマンドをPL/R手続きの本体より実行するとデータベースにアクセスできる。

  • pg.spi.exec (問い合わせ文の文字列)
  • pg.spi.prepare (character query, integer vector type_vector)
    後で実行する問い合わせ文を準備し、保存しておく。
  • pg.spi.execp (external pointer saved_plan, variable listvalue_list)
    pg.spi.prepare で事前に用意された問い合わせ文の実行
  • pg.spi.lastoid()
    pg.spi.exec または pg.spi.execp 経由で最後の問い合わせが挿入された行の OID を返す。
  • pg.quoteliteral (character SQL_string)
  • pg.quoteident (character SQL_string)
  • pg.thrownotice (character message)
     PostgreSQL NOTICE メッセージの発行
  • pg.throwerror (character message)
     PostgreSQL ERROR メッセージの発行。これ以降の処理が放棄され、現在のトランザクションも中止される。
  • pg.spi.factor (data.frame data)
    R のデータフレームを入力として受け入れ、すべての非数値カラムをファクターに変換する。

PostgreSQL サポート関数

  • install_rcmd (text R_code)
    文字列で与えられた R のコードをインタープリータにインストール
  • reload_plr_modules ()
    plr_modules テーブルより、R のコードを強制的にリロード
  • plr_singleton_array (float8 first_element)
  • plr_array_push (float8[] array, float8 next_element)
  • plr_array_accum (float8[] state_value, float8 next_element)
  • load_r_typenames()
  • r_typenames()
  • plr_environ()
    Postmaster が現在動作している環境の表示

集約関数

起動時にRモジュールを読み込む

PL/R はインタープリータの初期化中に R のコードを自動ローディングをすることをサポートしている。PL/R は plr_modules という R コードのモジュールを持つことになる特別なテーブルを使っている。このテーブルがある場合には、テーブルに定義されているモジュールがテーブルから持ってこられて、作成後すぐさま R インタープリータにロードされる。

以下に plr_modules の例を示す。

CREATE TABLE plr_modules (
  modseq int4,
  modsrc text
);

上の例でフィールド modseq はインストールの順序を制御し、フィールド modsrc には R のコードが入っている。以下にこれらの作成例を示す。

INSERT INTO plr_modules
 VALUES (0, 'pg.test.module.load <-function(msg) {print(msg)}');
 

PL/R は以下のように直接簡単に参照できる。

CREATE OR REPLACE FUNCTION pg_test_module_load(text) RETURNS TEXT AS '
  pg.test.module.load(arg1)
' LANGUAGE 'plr';

SELECT pg_test_module_load('hello world');
 pg_test_module_load 
---------------------
 hello world
(1 row)

R 関数名

トリガー手続き

PL/R ではトリガー手続きを書くことができる。PostgreSQL ではトリガーは引数がなく、trigger 型を返す関数で宣言する必要がある。

  • pg.tg.name
    CREATE TRIGGER ステートメントからのCREATE TRIGGER名
  • pg.tg.relid
    トリガー手続きを実行したテーブルのオブジェクトID
  • pg.tg.relname
    トリガー手続きを実行したテーブル名
  • pg.tg.when
    トリガー呼び出し型に依存する文字列 BEFORE または AFTER
  • pg.tg.level
    トリガー呼び出し型に依存する文字列 ROW または STATEMENT
  • pg.tg.op
    トリガー呼び出し型に依存する文字列 INSERT、UPDATE、または DELETE
  • pg.tg.new
  • pg.tg.old
  • pg.tg.args
    CREATE TRIGGER で作成された手続きの引数ベクトル
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS '

   if (pg.tg.op == "INSERT")
   {
     retval <- pg.tg.new
     retval[pg.tg.args[1]] <- 0
   }
   if (pg.tg.op == "UPDATE")
   {
     retval <- pg.tg.new
     retval[pg.tg.args[1]] <- pg.tg.old[pg.tg.args[1]] + 1
   }
   if (pg.tg.op == "DELETE")
     retval <- pg.tg.old

   return(retval)
' LANGUAGE plr;

CREATE TABLE mytab (num integer, description text, modcnt integer);

CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
   FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');

動作が確認されている環境

Red Hat 7.3, 8.0, & 9.
Mingw 上では?

利用例

テーブル内のデータをグラフ表示

ボロノイ図作成

 http://www.varlena.com/varlena/GeneralBits/Tidbits/bernier/art_66/graphingWithR.html

RGDAL の利用

PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL

TSPostgreSQL

Time Series Database Interface extensions for PostgreSQL

リンク

参考書


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Google
WWW を検索 OKADAJP.ORG を検索
Last-modified: 2015-03-01 (日) 01:15:59 (1449d)