PostgreSQLの文字コード変換で Hello World!!

これは、[PostgreSQL Advent Calendar 2018](https://qiita.com/advent-calendar/2018/postgresql)の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)」に変換されます。

```sql
-- クライアントエンコーディングを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](https://www.postgresql.jp/document/current/html/sql-createconversion.html)を使って独自に定義することも可能です。今回は、この文字コード変換の独自定義によりSQLを丸ごと```SELECT 'Hello World!!'```に書き換えます。

### 独自文字コード変換の定義

まずはSQLを丸ごと```SELECT 'Hello World!!'```に書き換えるC言語関数を作成します。

```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ディレクトリにインストールしている前提です。

```shell
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](https://www.postgresql.jp/document/current/html/sql-createfunction.html)でC言語関数をPostgreSQLに登録します。文字コード変換に使える関数の形式(引数や戻り値の型)は決まっているため、下記のとおり指定する必要があります。

```sql
CREATE FUNCTION hello_world(integer, integer, cstring, internal, integer) RETURNS void AS 'hello_world' LANGUAGE c STRICT VOLATILE;
```

登録した関数を使う独自の文字コード変換をCREATE CONVERSIONで定義します。今回は、SJISからUTF-8への文字コード変換として定義することにします。

```sql
CREATE CONVERSION hello_world FOR 'sjis' TO 'utf8' FROM hello_world;
```

また、この独自の文字コード変換を利用できるように、pg_conversionカタログのcondefaultカラムを直接更新します。更新後は、現在のセッションからログアウトします。次回のログインから独自文字コード変換をSJISからUTF-8へのデフォルトの文字コード変換として利用できます。

```sql
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がそのまま実行されることを確認します。

```sql
=# SELECT name FROM pg_settings ORDER BY name DESC LIMIT 1;                                                                                                                                                 name        
--------------------
 zero_damaged_pages
(1 row)

=# VACUUM ANALYZE;
VACUUM
```

次に、クライアントエンコーディングをSJISに設定変更すると、独自の文字コード変換が動き、**Hello World!!** が結果として返ってくることを確認します。

```sql
=# 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](https://github.com/MasaoFujii/pg_fallback_utf8_to_euc_jp)を参考にしてもらえればと思います。