Postgres的10进制与16进制互相转换

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';