オレオレSQLの作り方〜PostgreSQL編〜
オレオレ言語全盛期の炎暑の候、いかがお過ごしでしょうか。
オレオレSQLの作り方〜PostgreSQL編〜という事でPostgreSQLに、自作のSQL関数を組み込むやり方を紹介します。
簡単に言えば
select oreore();
select oreore(column1, column2) from table1;
自分で定義したC言語のoreore関数をDBに組み込んで使おうという事です。
固定値を返す関数
早速、固定で999を返す関数を作ってみます。
※バージョンはpostgresql8.1です。インストール等は省略します。
#include "postgres.h" #include "fmgr.h" PG_FUNCTION_INFO_V1(oreore); Datum oreore(PG_FUNCTION_ARGS) { // 固定で999を返します PG_RETURN_INT32(999); }
これをコンパイルし
gcc -shared -o oreore.so -I /usr/local/pgsql/include/server oreore.c
作成したオブジェクトの関数を呼び出す関数を定義します。
※ユーザ定義関数を定義するにはpostgresqlの管理者権限が必要
create function oreore() returns integer AS '/var/lib/pgsql/pg_func/oreore/oreore.so' language C with (isStrict);
#psql test < oreore.sql CREATE FUNCTION
では、早速作った関数を実行
# /usr/local/pgsql/bin/psql test Welcome to psql 8.1.11, the PostgreSQL interactive terminal. test=# select oreore(); oreore -------- 999 (1 row)
999が正しく表示されていますね。
オレオレSQL開発でのデバッグ方法
デバッグ作業の効率をあげるために、フロントエンドにメッセージを出力してみます。
elog関数を使用する事により、フロントエンドへメッセージを出力したり
トランザクションをアボートする事が可能です(※elog.hも参照下さい)
DEBUGレベルはpostgresql.confで設定出来ますが、既存の環境設定のままメッセージ出力するには
NOTICEを使用するのが容易です。
先ほどのoreore.cに1行追加します
oreore(PG_FUNCTION_ARGS) { elog(NOTICE, "デバッグのテストですよ"); // これを追加 PG_RETURN_INT32(999); }
#psql test ・・・ test=# select oreore(); NOTICE: デバッグのテストですよ oreore -------- 999 (1 row)
elogで指定したメッセージが出力されていますね。これでデバッグも容易に出来ます。
引数を取得
次に引数を取得してみます。
テーブル作成
test=# create table table1 (column1 integer, column2 text); CREATE TABLE test=# \d table1 Table "public.table1" Column | Type | Modifiers ---------+---------+----------- column1 | integer | column2 | text |
oreore.cを少し修正します。
oreore(PG_FUNCTION_ARGS) { int32 arg1 = PG_GETARG_INT32(0); text *arg2 = PG_GETARG_TEXT_P(1); char arg2_buff[128]; bzero(arg2_buff, sizeof(arg2_buff)); memcpy(arg2_buff, VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ); // 引数を表示 elog(NOTICE, "arg1=[%d] arg2=[%s]", arg1, arg2_buff); // 固定で999を返します PG_RETURN_INT32(999);}
早速、動かしてみます。
test=# select oreore(column1, column2) from table1; ERROR: function oreore(integer, text) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. test=#
最初に定義したoreore関数と型が異なる為に、oreore(integer, text) が存在しないエラーになります。
引数にintegerとtextを受け取るように、oreore.sqlを修正します。
引数を取得するには引数取得マクロ PG_GETARG_XXX を使用します。(※引数取得マクロはfmgr.hに記述)
ここで、先ほどの引数無しのoreore関数を削除しておきます。
登録した関数を削除するには、drop function(ユーザ関数)を使います。
drop function oreore(); create function oreore(integer, text) RETURNS INTEGER AS '/var/lib/pgsql/pg_func/oreore/oreore.so' language C with (isStrict);
# psql test < oreore.sql DROP FUNCTION CREATE FUNCTION
テーブルにデータを入れて実行してみます
test=# insert into table1 values(1, 'aaa'); INSERT 0 1 test=# insert into table1 values(2, 'bbb'); INSERT 0 1 test=# select * from table1; column1 | column2 ---------+--------- 1 | aaa 2 | bbb (2 rows) test=# select oreore(column1, column2) from table1; NOTICE: arg1=[1] arg2=[aaa] NOTICE: arg1=[2] arg2=[bbb] oreore -------- 999 999 (2 rows)
引数が取得できていますね。
HTMLを返すオレオレ関数
多くのWEBシステムでは、DBの値を使ってで動的にHTMLを生成します。
ネタ的要素が強いですが、いっその事SQLの結果でHTMLを返してみようという事で試しにやってみます。
まずテーブル作成
test=# create table html_test (id integer, name text); CREATE TABLE test=# insert into html_test values (1, 'よねむら'); INSERT 0 1 test=# insert into html_test values (2, 'もぺもぺ'); INSERT 0 1
オレオレ関数htmlを作成
#include "postgres.h" #include "fmgr.h" #include "lib/stringinfo.h" #include "utils/builtins.h" PG_FUNCTION_INFO_V1(html); Datum html(PG_FUNCTION_ARGS) { StringInfo msg; char *uname = DatumGetCString(DirectFunctionCall1(textout, PG_GETARG_DATUM(0))); msg = makeStringInfo(); appendStringInfo(msg, "<html><body>こんにちは%sさん</body></html>", uname); PG_RETURN_TEXT_P(DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(msg->data)))); }
関数登録
create function html(text) returns text AS '/var/lib/pgsql/pg_func/oreore/html.so' language C with (isStrict);
実行
※全データ取得 test=# select id, html(name) from html_test; id | html ----+-------------------------------------------------- 1 | <html><body>こんにちはよねむらさん</body></html> 2 | <html><body>こんにちはもぺもぺさん</body></html> (2 rows) ※idが1の結果だけを取得 test=# select id, html(name) from html_test where id=1; id | html ----+-------------------------------------------------- 1 | <html><body>こんにちはよねむらさん</body></html> (1 row) ※idが2の結果だけを取得 test=# select id, html(name) from html_test where id=2; id | html ----+-------------------------------------------------- 2 | <html><body>こんにちはもぺもぺさん</body></html> (1 row)
HTMLを直接返すというよりは、JSONを返したりする方が実用的でしょうか。
select json(column1, column2) from table1;
自分で定義した関数をRDBに登録して使う方法も活用次第では可能性が広がるので
是非、オレオレSQLを堪能してみて下さい。
※追記
石田さんにDatumGetTextPやCStringGetDatumを教えてもらい最後のoreore.cは書き換えてみました。
utils/builtins.hとlib/stringinfo.hは必見です。
石田さんありがとうございます。