PostgreSQL関数の登録

createfunction.sql

create function uncomplicate(text) returns varchar as $$
select
sub.a
|| sub.b
|| sub.c
|| sub.d
|| sub.e
|| sub.f
|| sub.g
|| sub.h
|| sub.i
|| sub.j
|| sub.k
|| sub.l
|| sub.m
|| sub.n
|| sub.o
|| sub.p
from
(
  select
    lpad(to_hex(get_byte(decode(substring($1, 1,  2), 'hex'), 0) + 1), 2, '0') as a
  -- 文字取り出し
  -- byteaに戻す
  -- 1byte取り出す(整数型に変更)

  -- 16進表現に戻す
  -- 2桁に揃える
  , lpad(to_hex(get_byte(decode(substring($1, 3,  2), 'hex'), 0) + 1), 2, '0') as b
  , lpad(to_hex(get_byte(decode(substring($1, 5,  2), 'hex'), 0) + 1), 2, '0') as c
  , lpad(to_hex(get_byte(decode(substring($1, 7,  2), 'hex'), 0) + 1), 2, '0') as d
  , lpad(to_hex(get_byte(decode(substring($1, 9,  2), 'hex'), 0) + 1), 2, '0') as e
  , lpad(to_hex(get_byte(decode(substring($1, 11, 2), 'hex'), 0) + 1), 2, '0') as f
  , lpad(to_hex(get_byte(decode(substring($1, 13, 2), 'hex'), 0) + 1), 2, '0') as g
  , lpad(to_hex(get_byte(decode(substring($1, 15, 2), 'hex'), 0) + 1), 2, '0') as h
  , lpad(to_hex(get_byte(decode(substring($1, 17, 2), 'hex'), 0) + 1), 2, '0') as i
  , lpad(to_hex(get_byte(decode(substring($1, 19, 2), 'hex'), 0) + 1), 2, '0') as j
  , lpad(to_hex(get_byte(decode(substring($1, 21, 2), 'hex'), 0) + 1), 2, '0') as k
  , lpad(to_hex(get_byte(decode(substring($1, 23, 2), 'hex'), 0) + 1), 2, '0') as l
  , lpad(to_hex(get_byte(decode(substring($1, 25, 2), 'hex'), 0) + 1), 2, '0') as m
  , lpad(to_hex(get_byte(decode(substring($1, 27, 2), 'hex'), 0) + 1), 2, '0') as n
  , lpad(to_hex(get_byte(decode(substring($1, 29, 2), 'hex'), 0) + 1), 2, '0') as o
  , lpad(to_hex(get_byte(decode(substring($1, 31, 2), 'hex'), 0) + 1), 2, '0') as p
) as sub
;

$$ language sql;