オレオレSQLの作り方〜PostgreSQL編〜

PostgreSQL C

オレオレ言語全盛期の炎暑の候、いかがお過ごしでしょうか。

オレオレ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で接続しなおすと

#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を堪能してみて下さい。


オレオレSQLの作り方〜MySQL編〜へ続く...


※追記
　石田さんにDatumGetTextPやCStringGetDatumを教えてもらい最後のoreore.cは書き換えてみました。
　utils/builtins.hとlib/stringinfo.hは必見です。
　石田さんありがとうございます。