postgres的10进制与16进制互相转换 1.10进制转16进制 postgres里面有一个内置的10进制转16进制的函数:to_hex(int)/to_hex(bigint) [postgres@localhost ~]$ psql password: psql (9.1.3) type help for help. postgres=# select to_hex(9); to_hex --------
postgres的10进制与16进制互相转换
1.10进制转16进制
postgres里面有一个内置的10进制转16进制的函数:to_hex(int)/to_hex(bigint)
[postgres@localhost ~]$ psql
password: 
psql (9.1.3)
type help for help.
postgres=# select to_hex(9);
 to_hex 
--------
 9
(1 row)
postgres=# select to_hex(17);
 to_hex 
--------
 11
(1 row)
postgres=# select to_hex(31);
 to_hex 
--------
 1f
(1 row)
postgres=# select to_hex(255);
 to_hex 
--------
 ff
(1 row)
postgres=# select to_hex(256);
 to_hex 
--------
 100
(1 row)
2.16进制转10进制 
没有内置的，参考网上的一个例子
postgres=# create function
hex_to_dec(in_hex text)
returns int
immutable strict language sql as
$body$
  select cast(cast(('x' || cast($1 as text)) as bit(8)) as int);
$body$;
create function
postgres=# select hex_to_dec('1f');
 hex_to_dec 
------------
         31
(1 row)
postgres=# select hex_to_dec('ff');
 hex_to_dec 
------------
        255
(1 row)
postgres=# select hex_to_dec('fe');
 hex_to_dec 
------------
        254
(1 row)
postgres=# select hex_to_dec('09');
 hex_to_dec 
------------
          9
(1 row)
postgres=# select hex_to_dec('11');
 hex_to_dec 
------------
         17
(1 row)
内置的函数说明,支持输入是int或者bigint类型 
to_hex(int/bigint): 
create or replace function to_hex(integer)
  returns text as
'to_hex32'
  language internal immutable strict
  cost 1;
alter function to_hex(integer)
  owner to postgres;
comment on function to_hex(integer) is 'convert int4 number to hex';
create or replace function to_hex(bigint)
  returns text as
'to_hex64'
  language internal immutable strict
  cost 1;
alter function to_hex(bigint)
  owner to postgres;
comment on function to_hex(bigint) is 'convert int8 number to hex';