こんにちは、habracheloveki！ 皆さんの多くは、ビジネスロジックをストアドファンクション/プロシージャの形式でDBMSに配置することに直面しており、クライアントにとって使いやすくなっています。 これには長所と短所の両方があります。 今日は、Cで書かれた
PostgreSQLでストアド関数を作成する方法をお伝えしたいと思います。この記事には、それらを使い始めるために知っておく必要のある基本事項が含まれています。
ユーザー機能の説明
PostgreSQLでは現在、次のタイプのユーザー定義関数を定義できます。
- SQL関数
- C関数
- 手続き言語の関数（ plpgsql 、 pltcl 、 plperlなど）
SQL関数は、1つ以上の
SQLクエリが存在する本体の関数であり、最後のクエリの結果が返された結果です。 さらに、返された結果が
voidでない場合、
INSERT 、
UPDATE 、または
DELETEを
RETURNING構造とともに使用できます。
C関数は静的および動的にロードされます。 静的にロードされた（内部関数とも呼ばれます）は、データベースクラスターが初期化されるときにサーバー上に作成され、動的にロードされたものはサーバーの要求に応じてロードされます。
手続き型言語の関数では、適切な拡張機能を作成する必要があり、一部の言語には、信頼できるものと信頼できないものの2つのタイプがあります（後者の場合、ユーザーアクションを制限する方法はありません）。 基本的な
PostgreSQLパッケージには
plpgsql 、
pltcl 、
plperl 、および
plpythonが含まれてい
ます 。他の言語のリストは
ここにあります 。 手続き型言語の拡張機能は、
SQLを介して作成され
ます 。
CREATE EXTENSION pltcl;
または、コンソールから（plpythonは信頼できない形式でのみ利用可能です）：
createlang plpythonu
Cで動的にロードされる関数
Cで動的に読み込まれる関数は、動的に読み込まれる（または共有される）ライブラリに含まれ、関数が最初に呼び出されたときに読み込まれます。 そのような関数を作成する例：
CREATE OR REPLACE FUNCTION grayscale ( r double precision, g double precision, b double precision ) RETURNS double precision AS 'utils', 'grayscale' LANGUAGE C STRICT;
この例では、
グレースケール関数（3つの
floatパラメーターを持ち、
floatを返す）を作成します。これは、
utilsダイナミックライブラリにあります。
STRICTキーワードは、少なくとも1つの引数が
NULLの場合に関数が
NULLを返すように使用され
ます。絶対パスが指定されていない場合、
dynamic_library_path変数で指定されたディレクトリを意味し、その値は次のように表示できます。
SELECT current_setting ( 'dynamic_library_path' );
変数の値または動的ライブラリへのパスが
$ libdirで始まる場合、
$ libdirは
PostgreSQLライブラリを含むディレクトリへのパスに置き換えられます。これは、コンソールコマンドを使用して確認できます。
pg_config --pkglibdir
ライブラリのロードは、
PostgreSQLデーモンが実行されているユーザー権限（通常は
postgres ）で実行されるため、このユーザーはライブラリへのアクセス権限を持っている必要があります。
関数には、
バージョン0 （非推奨）と
バージョン1の 2種類の規則があります。
バージョン0の機能は移植性
がなく、機能が制限されているため、
バージョン1の機能
がさらに暗示されています。
バージョン1を使用していることを示すには、関数を定義する前に特別なマクロでマークする必要があります。
PG_FUNCTION_INFO_V1(grayscale);
動的ライブラリ構造
各ライブラリには、特定のマジックブロック（ファイルの数に関係なく1つ）が必要です。そのため、たとえば、古いバージョンの
PostgreSQLサーバーと、ライブラリが構築される
PostgreSQLのバージョンなどの不一致を検出できます。 このブロックは次のように宣言されます。
#include <fmgr.h> #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif
必要に応じて、ライブラリをロードした後に呼び出される
_PG_init初期化
関数 （パラメーターを持たず
voidを返す）、およびライブラリをアンロードする前に呼び出される
_PG_fini初期化関数（
_PG_initと同じシグネチャを
持つ ）を
定義できます。 ドキュメントには、ライブラリが現在アンロードされていないことが記載されているため、
_PG_fini関数
が呼び出されること
はありません。 関数の例：
void _PG_init() { createLog(); } void _PG_fini() { destroyLog(); }
ライブラリの関数には特定の種類があります。マクロ、引数の受け取り、結果の返送、およびその他の操作のために、特別なマクロが提供されます（以下で詳しく説明します）。
Datum grayscale(PG_FUNCTION_ARGS) { float8 r = PG_GETARG_FLOAT8(0); float8 g = PG_GETARG_FLOAT8(1); float8 b = PG_GETARG_FLOAT8(2); PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b); }
データ型
関数で使用される基本的なデータ型は、次の3つの型に分類されます。
- 値によって送信される固定長
- 固定長、標識付き
- ポインターによって渡される可変長
最初のタイプのタイプのサイズは、1、2、または4バイトです（プラットフォームの
sizeof（データム）が 8の場合は8）。 独自のタイプを定義するとき（たとえば
typedefを使用 ）、すべてのアーキテクチャでサイズが同じであることを確認する必要があります。
ポインターによって渡される固定長型は構造体です。
pallocを
使用して 、それら（および第3種の型）にメモリを割り当てる必要があります。次に例を示します。
typedef struct { float r, g, b, a; } Color; Color *color = (Color*)palloc(sizeof(Color));
3番目のタイプのタイプの場合、タイプ全体のサイズ（データサイズ+フィールドサイズ）を格納するためのフィールド（4バイト）と、実際、このフィールドの後ろに連続して配置されるデータ自体を定義する必要があります。 これは、次の形式の構造を使用して実行できます。
typedef struct { int32 length; char data[1]; } text;
タイプサイズのフィールドの値は、
SET_VARSIZEマクロを使用して暗黙的に設定されます。 ポインターによって渡される可変長タイプを操作するための他のマクロ：
char data[10]; ... text *string = (text*)palloc(VARHDRSZ + 20);
Cと
SQLの関数のタイプ間の対応は、
この表に示され
ています。
ポインタによって転送されるタイプに関しては、このポインタが指すデータは変更できないことに注意してください。これは、ディスク上に直接配置されたデータである可能性があり、損傷につながる可能性があるためです。 結果は完全に楽しいものではありません。
関数構造
関数の署名は次のようになります。
Datum grayscale(PG_FUNCTION_ARGS);
データは、基本的にポインタの
typedefである関数の戻り値用の特別な型です。 マクロ
PG_FUNCTION_ARGSは 、関数のパラメーターに関するメタ情報（呼び出しのコンテキスト、値が
NULLかどうかなど）を含む構造へのポインターに展開されます。 関数の引数には、
PG_GETARG_ *マクロを使用してアクセスします。
float8 r = PG_GETARG_FLOAT8(0);
SQL関数が
STRICTなしで宣言されている場合、PG_ARGISNULLを使用して引数の値が
NULLかどうかを確認でき
ます 。
PG_RETURN_NULLを介して
NULLとして関数の結果を返すことができ
ます 。 例として、
STRICTを使用しない関数実装がどのように見えるかを見てみましょう。
Datum grayscale(PG_FUNCTION_ARGS) { if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(2)) { PG_RETURN_NULL(); } float8 r = PG_GETARG_FLOAT8(0); float8 g = PG_GETARG_FLOAT8(1); float8 b = PG_GETARG_FLOAT8(2); PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b); }
機能例
さて、Cでストアド関数を書く方法を知ったので、例をまとめて見てみましょう。 環境は次のとおりです。
- オペレーティングシステム：Ubuntu 12.10
- PostgreSQLバージョン：9.3
- コンパイラ：gcc 4.7.2
次の内容でutils.cファイルを作成します。
#include <postgres.h> #include <fmgr.h> #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif PG_FUNCTION_INFO_V1(grayscale); Datum grayscale(PG_FUNCTION_ARGS) { float8 r = PG_GETARG_FLOAT8(0); float8 g = PG_GETARG_FLOAT8(1); float8 b = PG_GETARG_FLOAT8(2); PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b); }
次に、utils.cをオブジェクトファイルにコンパイルします。
位置に依存しないコードを使用する必要があります（
gccの場合、これは
fpicオプションです）。
cc -I/usr/local/pgsql/include/server -fpic -c utils.c
pg_config --includedir-serverコマンドは、ヘッダーファイルがあるディレクトリの場所を示します。 オブジェクトファイルを動的ライブラリとしてリンクします（すべてが
正常な場合は、
utils.so動的ライブラリが必要です。これを
/ usr / local / pgsql / libにコピーします）。
cc -shared -L/usr/local/pgsql/lib -lpq -o utils.so utils.o
次に、データベースに接続して、その中に
grayscale_c関数を作成し、いくつかのオプションを示します。
CREATE OR REPLACE FUNCTION grayscale_c ( r double precision, g double precision, b double precision ) RETURNS double precision AS 'utils', 'grayscale' LANGUAGE C STRICT VOLATILE COST 100;;
そのパフォーマンスを確認します。
SELECT grayscale_c ( 0.6, 0.5, 0.5 );
しかし、それだけではありません。 この関数を同様の関数と比較しますが、plpgsqlで実行します。
grayscale_plpgsqlと呼び
ましょう ：
CREATE OR REPLACE FUNCTION grayscale_plpgsql ( r double precision, g double precision, b double precision ) RETURNS double precision AS $BODY$ BEGIN RETURN 0.299 * r + 0.587 * g + 0.114 * b; END $BODY$ LANGUAGE plpgsql STRICT VOLATILE COST 100;
そして、いくつかのテストをしましょう：
CREATE TABLE color AS SELECT random () AS r, random () AS g, random () AS b FROM generate_series ( 1, 1000000 ); SELECT grayscale_c ( r, g, b ) FROM color;
ちょっとしたチェック：
SELECT * FROM color WHERE grayscale_c ( r, g, b ) != grayscale_plpgsql ( r, g, b );
非常に良い結果です。
これまで見てきたように、動的にロードされる関数をCで作成することはそれほど難しくありません。 原則として、実装には困難が潜んでいます。
PSご清聴ありがとうございました。
参照：