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