PostgreSQLでは様々な言語を使ってユーザー定義関数(他のDBMSでは「ストアド・プロシジャ」と呼ばれている)を書くことができるだけでなく,任意の言語のユーザー定義関数を書く枠組み(言語ハンドラ)をユーザーが自由に追加できる。このメリットを生かし,PostgreSQLウォッチ第5回で紹介したPL/Javaをはじめ,PL/Rubyなど,様々なものが開発されている。
今回紹介するのはWeb記述スクリプト言語で有名なPHPでストアド・プロシジャが書けるPL/PHPである。
筆者を始め,特にPostgreSQLのユーザーには昔からPHPのファンが多い。PHPの豊富な機能がストアド・プロシジャとして使えるのであれば,それだけでもメリットがある。
さらにアプリケーション用に作成した関数がストアドプロシジャで使えるようになれば,無駄な開発を減らしたり,過去の資産を活かすことができる利点も期待できる。場合によっては,クライアント・サイドのDB処理をサーバー・サイドに移すことにより,クライアントとPostgreSQLの間の通信を減らして実行効率を上げることさえできるかもしれない。
PL/PHPの導入
早速PL/PHPを導入してみよう。PL/PHPのソースやドキュメントはhttp://plphp.commandprompt.com/から入手できる。
PL/PHPをインストールするためには,以下のものが必要である。
PostgreSQLがインストール済みでなければならないのは当然だが,PHPの方はインストールの必要はない。逆に,Apache用のPHPモジュールがインストールしてあってもそのモジュールはPL/PHPでは使用できない。
PL/PHPは,PostgreSQLに対するパッチの形で提供されている。現時点での最新バージョンは1.0RC1である。以下のURLから入手できる。
http://www.commandprompt.com/community/plphprc1.tar.gz
これを/tmp/plphprc1.tar.gzとして置いておく。
インストールはPostgreSQLのスーパユーザー(通常postgres)で行う。ここでは,PostgreSQL 7.4.2とPHP 4.3.6を使う。インストールに使ったPostgreSQLのソースは/usr/local/src/postgresql-7.4.2に展開済であるものとする。PHPのソースは/tmp/php-4.3.6.tar.gzに置いてあるものとする(PHPのソースはhttp://www.php.netから入手できる)。
$ cd /tmp
$ tar xfz plphprc1.tar.gz
$ tar xfz php-4.3.6.tar.gz
$ cd php-4.3.6
$ ./configure
$ make libphp4.la
$ cd /usr/local/src/postgresql-7.4.2
$ patch -b -p1 < /tmp/plphprc1/plphp.patch
$ cd src/pl/plphp
ここでMakefileを修正する。7行目の
php_src = $(php_path)
を
php_src = /tmp/php-4.3.6
に変更する。
$ cd src/pl/plphp
$ make
コンパイラのバージョンが古めだと,ここでエラーになるかも知れない(筆者の環境 Vine Linux 2.6r4ではそうだった)。
cp /tmp/php-4.3.6/main/php.h ./
patch php.h < php.h.patch
patching file php.h
gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../../../src/include/ -I/tmp/php-4.3.6/include/ -I/tmp/php-4.3.6/main/ -I//tmp/php-4.3.6/Zend/ -I/tmp/php-4.3.6 -I/tmp/php-4.3.6/TSRM/ -shared -L/tmp/php-4.3.6/.libs -L/usr/local/lib -c -o plphp.o plphp.c
plphp.c: In function `plphp_call_handler':
plphp.c:1272: parse error before `trv'
plphp.c:1276: `trv' undeclared (first use in this function)
plphp.c:1276: (Each undeclared identifier is reported only once
plphp.c:1276: for each function it appears in.)
plphp.c:1069: warning: variable `complete_php_source' might be clobbered by `longjmp' or `vfork'
plphp.c:1087: warning: variable `plphp_trigger_string' might be clobbered by `longjmp' or `vfork'
plphp.c:1129: warning: variable `__dest' might be clobbered by `longjmp' or `vfork'
make: *** [plphp.o] エラー 1
その場合は,plphp.cの1271行目と1272行目
if (Z_TYPE_P(ret_val) != IS_STRING) elog(ERROR,"plphp: Expected trigger to return None or a String");
HeapTuple trv;
の上下を入れ替えて
HeapTuple trv;
if (Z_TYPE_P(ret_val) != IS_STRING) elog(ERROR,"plphp: Expected trigger to return None or a String");
とすればOKである。
$ make install
後は,PL/PHPを使いたいデータベースにPL/PHPの言語ハンドラを登録するために,以下をpsqlから実行する。ここでは,「php」というデータベースを作ってそこに登録することにする。
$ createdb -E EUC_JP php
$ psql php
Welcome to psql 7.4.2,the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
php=# CREATE FUNCTION plphp_call_handler() RETURNS LANGUAGE_HANDLER AS 'plphp' LANGUAGE C;
CREATE FUNCTION
php=# CREATE TRUSTED LANGUAGE plphp HANDLER plphp_call_handler;
CREATE LANGUAGE
これでPL/PHPが使えるようになった。
PL/PHP関数の作り方
PL/PHPユーザー定義関数の作り方は普通のユーザー定義関数とまったく同じである。すなわち,以下のようにすれば良い。
CREATE FUNCTION funcname (argument-types) RETURNS return-type AS '
PHPのコード
' LANGUAGE 'plphp';
関数の引数は$args[0],$args[1]...などでアクセスできる。
与えられた2つ引数の引数のうち,大きい方を返すごく簡単な関数の例を示す。
CREATE OR REPLACE FUNCTION plphp_max (INTEGER,INTEGER) RETURNS INTEGER AS '
if ($args[0] > $args[1]) {
return $args[0];
} else {
return $args[1];
}
' STRICT LANGUAGE 'plphp';
この例ではSTRICT属性を指定しているので,引数のうち一つでもNULLが含まれていると関数は呼ばれることなく呼び出し元にNULLが返る。もし関数の中で明示的にNULLをチェックし,処理を分岐したい場合はSTRICT属性を外し,PHP関数のisset()が使える。また,関数の結果としてNULLを返したい場合は単に
return;
とすればよい。
php.iniの扱い
PL/PHP関数もphp.iniを読み込む。したがって,目的に応じてきちんとphp.iniを設定しておく必要がある。
function文の扱いについて
PL/PHP関数の中でも,もちろんfunction文によってphp関数を定義できる。例を示す。
CREATE OR REPLACE FUNCTION plphp_max (INTEGER,INTEGER) RETURNS INTEGER AS '
function f1($a1,$a2)
{
if ($a1 > $a2) {
return $a1;
} else {
return $a2;
}
}
return f1($args[0],$args[1]);
' STRICT LANGUAGE 'plphp';
php=# select f1(1,2);
f1
----
1
(1 row)
しかし,ここで問題が一つある。同じセッションの中では,2回目以降の呼び出しでエラーになってしまうのである。
ERROR: plphp: fatal error...
これを回避するためには,既にその関数が定義済の場合は,function文を呼び出さないようにすれば良い。
CREATE OR REPLACE FUNCTION plphp_max (INTEGER,INTEGER) RETURNS INTEGER AS '
if (!function_exists(f1))
{
function f1($a1,$a2)
{
if ($a1 > $a2) {
return $a1;
} else {
return $a2;
}
}
}
return f1($args[0],$args[1]);
' STRICT LANGUAGE 'plphp';
DBアクセス
PHPにもPostgreSQLのアクセス関数が付属しているが,もちろんこれはフロントエンドとしてDBにアクセスするためのものであり,PL/PHP関数では使えない。そこで,spi_exec_query()という関数が提供されている。
CREATE OR REPLACE FUNCTION simple_select() RETURNS TEXT AS '
$ret = spi_exec_query("SELECT relname FROM pg_class",1);
return $ret[0]["relname"];
' STRICT LANGUAGE 'plphp';
php=# select simple_select();
simple_select
-------------------
table_constraints
(1 row)
spi_exec_query()の第1引数は問い合わせで,SELECT文の他,UPDATEなどの任意のSQL文が書ける。第2引数はオプションで,問い合わせの制限件数である。
spi_exec_query()の戻値は結果を格納した配列で,2次元配列になっていて,
結果行のインデックス,列名の順に指定する。
行を返す関数
先ほどの例では,関数は1個しか結果を返せなかった。PostgreSQLでは,複数行を返す関数をSETOFというキーワードを使って定義できる。ただし,関数が返す型をあらかじめCREATE TYPEで作成しておく必要がある。
CREATE TYPE mytype AS (t1 TEXT);
CREATE OR REPLACE FUNCTION multi_select() RETURNS SETOF mytype AS '
$ret = spi_exec_query("SELECT relname FROM pg_class",10);
return $ret;
' STRICT LANGUAGE 'plphp';
php=# SELECT * FROM multi_select();
t1
--------------------------
table_constraints
table_privileges
tables
triggered_update_columns
triggers
usage_privileges
view_column_usage
pg_xactlock
view_table_usage
views
(10 rows)
複数列,複数行を返す関数
PL/PHPでも複数列,複数行を返す関数を作ることができる。先ほどとの違いは,値を返す配列を作っておくことだけである。
CREATE TYPE mytype2 AS (relaname TEXT,reltype INTEGER);
CREATE OR REPLACE FUNCTION multi_select2() RETURNS SETOF mytype2 AS '
$ret = spi_exec_query("SELECT relname,reltype FROM pg_class",10);
$val = array();
for ($i=0;$i<10;$i++)
{
$val[$i][0] = $ret[$i]["relname"];
$val[$i][1] = $ret[$i]["reltype"];
}
return $val;
' STRICT LANGUAGE 'plphp';
値を返す配列$valは2次元配列で,行インデックス,列インデックスの順に値を設定すればよい。
駆け足でPL/PHPを紹介した。PHPは初心者にも分かりやすく,機能が豊富であることがメリットである。そのメリットがPostgreSQLのユーザー定義関数でも使えるようになったのは嬉しい。PHP/PHPの今後に注目したい。
■著者紹介
石井達夫(いしい・たつお)氏
1984年,SRA入社。主にUNIX関連の開発に従事するかたわら,95年からPostgreSQLのメーリング・リストを主宰。現在はオープンソースソリューション部でPostgreSQL関連のビジネス活動を技術支援。著書に『PostgreSQL完全攻略ガイド』(技術評論社),『PHPxPostgreSQLで作る最強Webシステム』(技術評論社),『PostgreSQL構築・運用ガイド』(日経BP,共著)などがある。日本PostgreSQLユーザ会会員。