これは、PostgreSQL Advent Calendar 2018の15日目の記事です。
はじめに
どんなSQLをPostgreSQLに投げても Hello World!! と結果を返してほしい場面が時にはあると思います。今回は、PostgreSQLの文字コード変換の仕組みを使って、どんなSQLを投げてもSELECT 'Hello World!!'を代わりに実行させてみたいと思います。
そもそもPostgreSQLの文字コード変換とは
PostgreSQLは、クライアントエンコーディングとデータベースエンコーディングが異なるとき、クライアントとデータベースの間でやりとりするSQL文やSQL実行結果について自動的に文字コード変換を行います。例えば、クライアントエンコーディングがSJIS、データベースエンコーディングがUTF-8の場合、SQL文中のSJIS「ア(0x8341)」はPostgreSQLによってUTF-8「ア(U+30A2)」に変換されます。
-- クライアントエンコーディングをSJISに設定変更して、SJIS「ア(0x8341)」をtestテーブルに格納する
=# CREATE TABLE test (chr TEXT);
=# SET client_encoding TO sjis;
=# \copy test from program 'echo "8341" | xxd -p -r'
-- クライアントエンコーディングをUTF-8に戻して、testテーブルに格納された文字がUTF-8「ア(U+30A2)」であることを確認する
=# SET client_encoding TO utf8;
=# SELECT chr, to_hex(ascii(chr)) FROM test;
chr | to_hex
-----+--------
ア | 30a2
(1 row)
この文字コード変換のロジックはPostgreSQLがデフォルトで提供しますが、CREATE CONVERSIONを使って独自に定義することも可能です。今回は、この文字コード変換の独自定義によりSQLを丸ごとSELECT 'Hello World!!'に書き換えます。
独自文字コード変換の定義
まずはSQLを丸ごとSELECT 'Hello World!!'に書き換えるC言語関数を作成します。
#include "postgres.h"
#include "funcapi.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(hello_world);
Datum
hello_world(PG_FUNCTION_ARGS)
{
unsigned char *dest = (unsigned char *) PG_GETARG_CSTRING(3);
/* 文字コード変換後の文字列として Hello World のSQLを設定 */
strcpy((char *)dest, "SELECT 'Hello World!!'");
PG_RETURN_VOID();
}
C言語関数をコンパイルします。以下では、PostgreSQLは/pgsqlディレクトリにインストールしている前提です。
gcc -fpic -c hello_world.c -I/pgsql/include/server/
# Linuxの場合
gcc -shared -o /pgsql/lib/hello_world.so hello_world.o
# Macの場合
gcc -bundle -bundle_loader /pgsql/bin/postgres -o /pgsql/lib/hello_world.so hello_world.o
CREATE FUNCTIONでC言語関数をPostgreSQLに登録します。文字コード変換に使える関数の形式(引数や戻り値の型)は決まっているため、下記のとおり指定する必要があります。
CREATE FUNCTION hello_world(integer, integer, cstring, internal, integer) RETURNS void AS 'hello_world' LANGUAGE c STRICT VOLATILE;
登録した関数を使う独自の文字コード変換をCREATE CONVERSIONで定義します。今回は、SJISからUTF-8への文字コード変換として定義することにします。
CREATE CONVERSION hello_world FOR 'sjis' TO 'utf8' FROM hello_world;
また、この独自の文字コード変換を利用できるように、pg_conversionカタログのcondefaultカラムを直接更新します。更新後は、現在のセッションからログアウトします。次回のログインから独自文字コード変換をSJISからUTF-8へのデフォルトの文字コード変換として利用できます。
BEGIN;
UPDATE pg_conversion SET condefault = 'f' WHERE conname = 'sjis_to_utf8';
UPDATE pg_conversion SET condefault = 't' WHERE conname = 'hello_world';
COMMIT;
-- 現在のセッションをログアウト
\q
これで準備は整いました。
文字コード変換によるHello World!!
では、文字コード変換によって Hello World!! できるか試してみます。
まずはクライアントエンコーディングがSJISでないときは、投げられたSQLがそのまま実行されることを確認します。
=# SELECT name FROM pg_settings ORDER BY name DESC LIMIT 1; name
--------------------
zero_damaged_pages
(1 row)
=# VACUUM ANALYZE;
VACUUM
次に、クライアントエンコーディングをSJISに設定変更すると、独自の文字コード変換が動き、Hello World!! が結果として返ってくることを確認します。
=# SET client_encoding TO sjis;
SET
=# SELECT name FROM pg_settings ORDER BY name DESC LIMIT 1;
?column?
---------------
Hello World!!
(1 row)
=# VACUUM ANALYZE;
?column?
---------------
Hello World!!
(1 row)
というわけで無事に文字コード変換で Hello World!! できていることが確認できました!!
参考
CREATE CONVERSIONは、本来は、PostgreSQLがデフォルトでは提供していない文字コード変換を実現するためのものです。まじめに独自の文字コード変換を定義する必要がある場合は、pg_fallback_utf8_to_euc_jpを参考にしてもらえればと思います。