Postgresqlで16進数のシリアル番号を10進数に相互変換するファンクションを書いた

X.509証明書のシリアル番号を10進数でDBに保存しようかと思ってたけど、bigintの範囲も超えちゃうのでけっきょく16進数の文字列のまま保存することにした。
いちおう、相互変換できるファンクションは書いたので、もったいないので残しておく。
PL/pgSQLは初めて書いたけど、マニュアルみながらでもけっこう書けるもんなんだね。わりといろいろできて面白い。

PL/pgSQL書いてるときにフムフムと思ったこと

http://www.postgresql.jp/document/9.3/html/plpgsql.html

制御構文はひと通り用意されてる。

SQLの実行結果を変数に入れる場合はEXECUTE sql INTO varが使える。

他には実行結果をRECORD型で受けるLOOPを回してるのもあったけど、1件取るだけなら、EXCUTE使うほうがいいと思った。

CREATE FUNCTION from_hex(t text) RETURNS integer AS $$
  DECLARE
    r RECORD;
  BEGIN
    FOR r IN EXECUTE 'SELECT x'''||t||'''::integer AS hex' LOOP
      RETURN r.hex;
    END LOOP;
  END
$$ LANGUAGE plpgsql;

functionの引数で空白を渡しても、なんかトリムされてるなあ。困る。

— シラカワ ヒロユキ (@shrkwh) 2014, 8月 6

おお、引数の型がcharだとトリムされるけど、varcharにしたらそのままだ。へー

— シラカワ ヒロユキ (@shrkwh) 2014, 8月 6

serialnumber_hexのほうはもともとsepの型をcharでやってたんだけど、これだと空白渡すと自動でトリムされて困った。varcharならトリムとかされない。

あと、substrは1オリジンだった。このへんて言語によっていろいろな気がしたりするけど思い込みかも。