Selfkleptomaniac

PostgreSQL8.3の自動キャスト

以前のエントリでPostgreSQL8.3への移行は型キャストが自動的に行われないため結構大変だと書いたが、PostgreSQL8.3にはCREATE CASTという新しいキャストの定義を記述できる機能がある。Let’s Postgreｓの記事で知った。風説の流布もいいところだった。以下の内容はLet’s Postgresの記事にtimestampを追加しただけ。まったくもって無知の涙である。

以前サンプルにしていたデータでやってみる。

db_test=# SELECT * FROM test_table;            
      datetime       
---------------------
 2008-06-30 01:23:45
(1 row)

timestamp型のデータが格納されたテーブルがある。PostgreSQL7.4なら、

db_test=# SELECT SUBSTR(datetime, 1, 4) FROM test_table;
 substr
--------
 2008
(1 row)

こんな風にtimestamp型のデータにSUBSTR関数を実行しても自動的にキャストされて処理されている。自動的にキャストしないPostgreSQL8.3でこのSQLを実行するとエラーになるのだが、以下の方法で回避できた。

まずtimestamp型の入出力関数を調べる。

db_test=# SELECT oid, typname, typinput, typoutput
     FROM pg_catalog.pg_type WHERE oid = 'timestamp'::regtype;
 oid  |  typname  |   typinput   |   typoutput   
------+-----------+--------------+---------------
 1114 | timestamp | timestamp_in | timestamp_out
(1 row)

textと違ってinやoutの前に「_」が入るようだ。

これを利用してtimestamp型のデータをtext型にキャストする関数を作成し、自動キャストのルールを作成してしまえばいい。

db_test=# CREATE FUNCTION timestamp2text(timestamp) RETURNS text AS 
'SELECT textin(timestamp_out($1))' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION
db_test=# CREATE CAST (timestamp AS text) 
WITH FUNCTION timestamp2text(timestamp) AS IMPLICIT;
CREATE CAST

これで準備完了。では試してみよう。

db_test=# SELECT SUBSTR(datetime, 1, 4) FROM test_table;
 substr 
--------
 2008
(1 row)

お見事。

ただし、インデックスの作成プランなど見直す箇所も多いだろうから、単純にこれでおしまいというわけではないけれども。