この記事の話題
暗黙の型変換を定義してその挙動を確認しました。
postgresql 9.3でためしてます。
具体的な問題
テーブルを作る
create table hoge(
id numeric (3, 0)
, valid numeric(1,0)
);
insertを発行する(psqlで実施)
postgres=# insert into hoge values (1, true);
ERROR: 列"valid"は型numericですが、式は型booleanでした
行 1: insert into hoge values (1, true);
^
HINT: 式を書き換えるかキャストしなければなりません
numericに対してbooleanは指定できない。
この記事では上記のSQLがうまく動かすとことその周辺まで書きます。
暗黙の型変換を定義
暗黙の型変換を定義することで、異なる型のCASTを定義することでInsertを可能にします。
具体的には以下の手順。
1.型変換をfunctionを定義
例：integerをboolに変換する関数b2nを定義
CREATE FUNCTION b2n(bool) RETURNS numeric(1) AS
'select
case
when $1 IS TRUE
then CAST(1 AS NUMERIC(1))
else CAST(0 AS NUMERIC(1))
end;'
LANGUAGE SQL;
2.castにfunctionを設定
integerをboolにするCASTを暗黙的に設定。（AS ASSIGNMENTは後述）
create cast(bool as numeric) WITH FUNCTION b2n(bool) AS ASSIGNMENT;
再実施
暗黙の型変換を定義して再実施するとInsertに成功する。
postgres=# insert into hoge values (1, true);
INSERT 0 1
postgres=# select * from hoge;
id | valid
----+-------
1 | 1
(1 行)
Insertできました、めでたい。
コンテキスト
postgresqlでの暗黙キャストには2種類コンテキストがある。
上記の例ではASSIGNMENTを設定した。
代入コンテキスト（ASSIGNMENT）
代入時に利用されるように指定する。デフォルト値（INSERTとかUPDATEとか）
任意のコンテキスト（IMPLICIT）
あらゆる文脈で呼ばれる（式とか）
コンテキストの設定による挙動の違い
コンテキストの設定による挙動の違いの例を記載します。
先ほどのboolをnumericに変換するキャストをASSIGNMENTを指定していた場合
postgres=# select 1+true;
ERROR: 演算子が存在しません: integer + boolean
行 1: select 1+true;
^
HINT: 指定名称、指定引数型に合う演算子がありません。明示的な型キャストが必要か
もしれません
trueに対してキャストは実施されないためこのようにエラーになります。
boolをnumericに変換するキャストをIMPLICITで指定していた場合
postgres=# select 1+true;
?column?
----------
2
(1 行)
暗黙的に式の中でboolに対してcastが実施されて結果が出力されます。
暗黙キャストの削除
ここまで試したことをなかったことにします。
（１）castを削除
drop cast(bool as numeric);
（２）型変換のfunctionを削除
drop function b2n(bool);
まとめ
既存のSQLを変えられないなど困った制約に対してこういうごまかしがききそうです。
ただ、性能面の劣化などはあるので計画的に使っていきましょう。