-
Notifications
You must be signed in to change notification settings - Fork 157
/
Copy pathbin.sql
139 lines (132 loc) · 4.74 KB
/
bin.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
create or replace function public.bin(n int)
returns bit
immutable
strict -- returns null if any parameter is null
parallel safe -- Postgres 10 or later
security invoker
language sql
set search_path = ''
AS $func$
select
case (case when pos = 0 then 1 else 33 - pos end)
when 1 then n::bit(1)
when 2 then n::bit(2)
when 3 then n::bit(3)
when 4 then n::bit(4)
when 5 then n::bit(5)
when 6 then n::bit(6)
when 7 then n::bit(7)
when 8 then n::bit(8)
when 9 then n::bit(9)
when 10 then n::bit(10)
when 11 then n::bit(11)
when 12 then n::bit(12)
when 13 then n::bit(13)
when 14 then n::bit(14)
when 15 then n::bit(15)
when 16 then n::bit(16)
when 17 then n::bit(17)
when 18 then n::bit(18)
when 19 then n::bit(19)
when 20 then n::bit(20)
when 21 then n::bit(21)
when 22 then n::bit(22)
when 23 then n::bit(23)
when 24 then n::bit(24)
when 25 then n::bit(25)
when 26 then n::bit(26)
when 27 then n::bit(27)
when 28 then n::bit(28)
when 29 then n::bit(29)
when 30 then n::bit(30)
when 31 then n::bit(31)
when 32 then n::bit(32)
end
from position(B'1' in n::bit(32)) as pos
$func$;
comment on function public.bin(n int) is 'Convert from an integer into a bit representation';
--TEST
DO $$
BEGIN
assert public.bin(-2147483648) = B'10000000000000000000000000000000';
assert public.bin(-4) = B'11111111111111111111111111111100';
assert public.bin(-3) = B'11111111111111111111111111111101';
assert public.bin(-2) = B'11111111111111111111111111111110';
assert public.bin(-1) = B'11111111111111111111111111111111';
assert public.bin(0) = B'0';
assert public.bin(1) = B'1';
assert public.bin(2) = B'10';
assert public.bin(3) = B'11';
assert public.bin(4) = B'100';
assert public.bin(8) = B'1000';
assert public.bin(16) = B'10000';
assert public.bin(2147483647) = B'1111111111111111111111111111111';
END
$$;
------------------------------------------------------------------------------------------------------------------------
create or replace function public.bin(n int, length int)
returns bit
immutable
strict -- returns null if any parameter is null
parallel safe -- Postgres 10 or later
security invoker
language sql
set search_path = ''
AS $func$
select
case length
when 1 then n::bit(1)
when 2 then n::bit(2)
when 3 then n::bit(3)
when 4 then n::bit(4)
when 5 then n::bit(5)
when 6 then n::bit(6)
when 7 then n::bit(7)
when 8 then n::bit(8)
when 9 then n::bit(9)
when 10 then n::bit(10)
when 11 then n::bit(11)
when 12 then n::bit(12)
when 13 then n::bit(13)
when 14 then n::bit(14)
when 15 then n::bit(15)
when 16 then n::bit(16)
when 17 then n::bit(17)
when 18 then n::bit(18)
when 19 then n::bit(19)
when 20 then n::bit(20)
when 21 then n::bit(21)
when 22 then n::bit(22)
when 23 then n::bit(23)
when 24 then n::bit(24)
when 25 then n::bit(25)
when 26 then n::bit(26)
when 27 then n::bit(27)
when 28 then n::bit(28)
when 29 then n::bit(29)
when 30 then n::bit(30)
when 31 then n::bit(31)
when 32 then n::bit(32)
end
$func$;
comment on function public.bin(n int, length int) is 'Convert from an integer into a bit representation with fixed length';
--TEST
DO $$
BEGIN
assert public.bin(-2147483648, 32) = B'10000000000000000000000000000000';
assert public.bin(-4, 32) = B'11111111111111111111111111111100';
assert public.bin(-3, 32) = B'11111111111111111111111111111101';
assert public.bin(-2, 32) = B'11111111111111111111111111111110';
assert public.bin(-1, 32) = B'11111111111111111111111111111111';
assert public.bin(0, 1) = B'0';
assert public.bin(0, 2) = B'00';
assert public.bin(0, 3) = B'000';
assert public.bin(1, 1) = B'1';
assert public.bin(2, 2) = B'10';
assert public.bin(3, 2) = B'11';
assert public.bin(4, 3) = B'100';
assert public.bin(8, 4) = B'1000';
assert public.bin(16, 5) = B'10000';
assert public.bin(2147483647, 31) = B'1111111111111111111111111111111';
END
$$;