PostgreSQL convert integer to bit
背景
昨天一位同事问我PostgreSQL 有没有十进制转二进制的函数.
看了一下目前PostgreSQL没有此类函数, 所以需要自己写一下.
正文
有2种方法,
第一种方法如下, 涉及符号与类型宽度(假设int为signed int).
do language plpgsql $$
declare
o_bit text;
o_len int;
i_num int;
i_conv int;
i_num_abs int;
i_res int;
i_mod int;
begin
o_len := 32;
i_num := -10234;
i_conv := 2;
i_num_abs := abs(i_num);
i_res := i_num_abs/i_conv;
i_mod := mod(i_num_abs,i_conv);
o_bit := i_mod::text;
loop
if i_res = 0 then
exit;
end if;
i_mod := mod(i_res,i_conv);
i_res := i_res/i_conv;
o_bit := i_mod||o_bit;
end loop;
o_len := o_len - char_length(o_bit) - 1;
o_bit := repeat('0', o_len)||o_bit;
if i_num >=0 then
o_bit := '0'||o_bit;
else
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
end;
$$;
创建函数如下 :
create or replace function i4tob(i_num int) returns varbit as $$
declare
o_bit text;
o_len int;
i_conv int;
i_num_abs int;
i_res int;
i_mod int;
begin
o_len := 32;
i_conv := 2;
i_num_abs := abs(i_num);
i_res := i_num_abs/i_conv;
i_mod := mod(i_num_abs,i_conv);
o_bit := i_mod::text;
loop
if i_res = 0 then
exit;
end if;
i_mod := mod(i_res,i_conv);
i_res := i_res/i_conv;
o_bit := i_mod||o_bit;
end loop;
o_len := o_len - char_length(o_bit) - 1;
if i_num >=0 then
null;
else
o_bit := repeat('0', o_len)||o_bit;
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
return o_bit::varbit;
end;
$$ language plpgsql;
测试 :
postgres=# select i4tob(-100);
NOTICE: 10000000000000000000000001100100
i4tob
----------------------------------
10000000000000000000000001100100
(1 row)
postgres=# select i4tob(100);
NOTICE: 1100100
i4tob
---------
1100100
(1 row)
postgres=# select i4tob(256);
NOTICE: 100000000
i4tob
-----------
100000000
(1 row)
postgres=# select i4tob(-256);
NOTICE: 10000000000000000000000100000000
i4tob
----------------------------------
10000000000000000000000100000000
(1 row)
另一种方法是使用位移算法 :
do language plpgsql $$
declare
o_bit text;
o_len int;
i_num int;
i_conv int;
i_num_abs int;
i_pos int;
begin
o_len := 32;
i_num := -1024;
i_conv := 2;
i_num_abs := abs(i_num);
i_pos := trunc((dlog1(i_num_abs))/0.693147180559945);
o_bit := mod(i_num_abs,i_conv)::text;
if i_pos >= 1 then
for i in 1..i_pos loop
o_bit := mod(i_num_abs>>i, i_conv)||o_bit;
end loop;
end if;
if i_num >=0 then
null;
else
o_len := o_len - char_length(o_bit) - 1;
o_bit := repeat('0', o_len)||o_bit;
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
end;
$$;
改成函数 :
create or replace function si32tob(i_num int) returns varbit as $$
declare
o_bit text;
o_len int;
i_conv int;
i_num_abs int;
i_pos int;
begin
o_len := 32;
i_conv := 2;
i_num_abs := abs(i_num);
i_pos := trunc((dlog1(i_num_abs))/0.693147180559945);
o_bit := mod(i_num_abs,i_conv)::text;
if i_pos >= 1 then
for i in 1..i_pos loop
o_bit := mod(i_num_abs>>i, i_conv)||o_bit;
end loop;
end if;
if i_num >=0 then
null;
else
o_len := o_len - char_length(o_bit) - 1;
o_bit := repeat('0', o_len)||o_bit;
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
return o_bit::varbit;
end;
$$ language plpgsql;
测试 :
digoal=# select si32tob(1);
NOTICE: 1
si32tob
---------
1
(1 row)
Time: 1.715 ms
digoal=# select si32tob(2);
NOTICE: 10
si32tob
---------
10
(1 row)
Time: 1.049 ms
digoal=# select si32tob(-2);
NOTICE: 10000000000000000000000000000010
si32tob
----------------------------------
10000000000000000000000000000010
(1 row)
Time: 1.520 ms
digoal=# select si32tob(234);
NOTICE: 11101010
si32tob
----------
11101010
(1 row)
Time: 1.094 ms
digoal=# select si32tob(-234);
NOTICE: 10000000000000000000000011101010
si32tob
----------------------------------
10000000000000000000000011101010
(1 row)
Time: 1.124 ms
以上函数参考wikihow提供的算法编写.
其他转换可参考本文末尾的函数进行修改.
实际上现在的版本不需要这么复杂
已经内置了转换方法，直接转换即可：
postgres=# select 123::bit(64);
bit
------------------------------------------------------------------
0000000000000000000000000000000000000000000000000000000001111011
(1 row)
postgres=# select 12322222222::bit(64);
bit
------------------------------------------------------------------
0000000000000000000000000000001011011110011101100011000010001110
(1 row)
postgres=# select 1::bit(64);
bit
------------------------------------------------------------------
0000000000000000000000000000000000000000000000000000000000000001
(1 row)
postgres=# select (-1)::bit(64);
bit
------------------------------------------------------------------
1111111111111111111111111111111111111111111111111111111111111111
(1 row)
postgres=# select (0)::bit(64);
bit
------------------------------------------------------------------
0000000000000000000000000000000000000000000000000000000000000000
(1 row)
参考
1. http://www.wikihow.com/Convert-from-Decimal-to-Binary
2. http://psoug.org/snippet/Convert-between-Decimal-Binary-Octal-and-Hex_536.htm
3. https://stackoverflow.com/questions/8316164/convert-hex-in-text-representation-to-decimal-number/8335376#8335376
4. https://stackoverflow.com/questions/25020950/postgresql-convert-bit-varying-to-integer
5. src/backend/optimizer/path/costsize.c
00096 #define LOG2(x) (log(x) / 0.693147180559945)
- oracle pl/sql 相关转换函数 :
SET serveroutput ON
CREATE OR REPLACE PACKAGE dbms_numsystem AS
FUNCTION bin2dec (binval IN CHAR ) RETURN NUMBER;
FUNCTION dec2bin (N IN NUMBER) RETURN VARCHAR2;
FUNCTION oct2dec (octval IN CHAR ) RETURN NUMBER;
FUNCTION dec2oct (N IN NUMBER) RETURN VARCHAR2;
FUNCTION hex2dec (hexval IN CHAR ) RETURN NUMBER;
FUNCTION dec2hex (N IN NUMBER) RETURN VARCHAR2;
END dbms_numsystem;
/
show errors
CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS
FUNCTION bin2dec (binval IN CHAR) RETURN NUMBER IS
i NUMBER;
digits NUMBER;
result NUMBER := 0;
current_digit CHAR(1);
current_digit_dec NUMBER;
BEGIN
digits := LENGTH(binval);
FOR i IN 1..digits LOOP
current_digit := SUBSTR(binval, i, 1);
current_digit_dec := TO_NUMBER(current_digit);
result := (result * 2) + current_digit_dec;
END LOOP;
RETURN result;
END bin2dec;
FUNCTION dec2bin (N IN NUMBER) RETURN VARCHAR2 IS
binval VARCHAR2(64);
N2 NUMBER := N;
BEGIN
WHILE ( N2 > 0 ) LOOP
binval := MOD(N2, 2) || binval;
N2 := TRUNC( N2 / 2 );
END LOOP;
RETURN binval;
END dec2bin;
FUNCTION oct2dec (octval IN CHAR) RETURN NUMBER IS
i NUMBER;
digits NUMBER;
result NUMBER := 0;
current_digit CHAR(1);
current_digit_dec NUMBER;
BEGIN
digits := LENGTH(octval);
FOR i IN 1..digits LOOP
current_digit := SUBSTR(octval, i, 1);
current_digit_dec := TO_NUMBER(current_digit);
result := (result * 8) + current_digit_dec;
END LOOP;
RETURN result;
END oct2dec;
FUNCTION dec2oct (N IN NUMBER) RETURN VARCHAR2 IS
octval VARCHAR2(64);
N2 NUMBER := N;
BEGIN
WHILE ( N2 > 0 ) LOOP
octval := MOD(N2, 8) || octval;
N2 := TRUNC( N2 / 8 );
END LOOP;
RETURN octval;
END dec2oct;
FUNCTION hex2dec (hexval IN CHAR) RETURN NUMBER IS
i NUMBER;
digits NUMBER;
result NUMBER := 0;
current_digit CHAR(1);
current_digit_dec NUMBER;
BEGIN
digits := LENGTH(hexval);
FOR i IN 1..digits LOOP
current_digit := SUBSTR(hexval, i, 1);
IF current_digit IN ('A','B','C','D','E','F') THEN
current_digit_dec := ASCII(current_digit) - ASCII('A') + 10;
ELSE
current_digit_dec := TO_NUMBER(current_digit);
END IF;
result := (result * 16) + current_digit_dec;
END LOOP;
RETURN result;
END hex2dec;
FUNCTION dec2hex (N IN NUMBER) RETURN VARCHAR2 IS
hexval VARCHAR2(64);
N2 NUMBER := N;
digit NUMBER;
hexdigit CHAR;
BEGIN
WHILE ( N2 > 0 ) LOOP
digit := MOD(N2, 16);
IF digit > 9 THEN
hexdigit := CHR(ASCII('A') + digit - 10);
ELSE
hexdigit := TO_CHAR(digit);
END IF;
hexval := hexdigit || hexval;
N2 := TRUNC( N2 / 16 );
END LOOP;
RETURN hexval;
END dec2hex;
END dbms_numsystem;
/
show errors