varbitx - PolarDB - Alibaba Cloud Documentation Center

The varbit extension that is provided in the PostgreSQL Community edition supports only simple BIT-type operation functions. The varbitx extension in PolarDB for PostgreSQL supports more BIT-type operations in more scenarios. These scenarios include real-time user profile recommendation, access control advertising, and ticketing.

Prerequisites

Your PolarDB for PostgreSQL cluster runs PostgreSQL 11.

Functions


Function	Description
`get_bit (varbit a, int b, int c) returns varbit`	Obtains a specified number c of bits that start at position b and returns a VARBIT-type string. For example, `get_bit('111110000011', 3, 5)` returns 11000.
`set_bit_array (varbit a, int b, int c, int[] d) returns varbit`	Changes the values of the bits that are specified by the subscript array d to values b (0 or 1), and fills the bits that exceed the original length with values c (0 or 1). For example, `set_bit_array('111100001111', 0, 1, array[1,15])` returns 1011000011111110.
`bit_count (varbit a, int b, int c, int d) returns int`	Counts the number of values b (0 or 1) that start at position c among a specified number d of bits. The bits that are beyond the specified length are not counted. For example, `bit_count('1111000011110000', 1, 5, 4)` returns 1.
`bit_count (varbit a, int b) returns int`	Counts the total number of values b (0 or 1). For example, `bit_count('1111000011110000', 1)` returns 8.
`bit_fill (int a, int b) returns varbit`	Fills a specified number b of bits with values a (0 or 1). For example, `bit_fill(0,10)` returns 0000000000.
`bit_rand (int a, int b, float c) returns varbit`	Obtains a random percentage c of bits from a specified number a of bits, and randomly fills the obtained bits with values b (0 or 1). For example, `bit_rand(10, 1, 0.3)` may return 0101000001.
`bit_posite (varbit a, int b, boolean c) returns int[]`	Returns a subscript array. The subscript array indicates the positions of the bits whose values are b (0 or 1). Subscripts start at 0. If the value of c is true, subscripts are returned in a positive sequence. If the value of c is false, subscripts are returned in a negative sequence. For example, `bit_posite ('11110010011', 1, true)` returns [0,1,2,3,6,9,10], and `bit_posite ('11110010011', 1, false)` returns [10,9,6,3,2,1,0].
`bit_posite (varbit a, int b, int c, boolean d) returns int[]`	Returns a subscript array. The subscript array indicates the positions of the bits whose values are b (0 or 1). The number of subscripts in the subscript array is c. Subscripts start at 0. If the value of d is true, subscripts are returned in a positive sequence. If the value of d is false, subscripts are returned in a negative sequence. For example, `bit_posite ('11110010011', 1, 3, true)` returns [0,1,2], and `bit_posite ('11110010011', 1, 3, false)` returns [10,9,6].
`get_bit_array (varbit a, int b, int c, int d) returns int[]`	Obtains a specified number c of bits that start at position b, identifies the bits whose values are d (0 or 1) among the obtained bits, and returns a subscript array. The subscript array indicates the positions of the identified bits. For example, `get_bit_array('111110000011', 3, 5, 1)` returns [3,4] for the specified 11000 bit string.
`get_bit_array (varbit a, int b, int[] c) returns int[]`	Obtains the bits that are specified by a subscript array c, identifies the bits whose values are b (0 or 1) among the obtained bits, and returns a subscript array. The subscript array indicates the positions of the identified bits. The bits that are not included in the subscript array c are not counted. For example, `get_bit_array('111110000011', 1, array[1,5,6,7,10,11])` returns [1,10,11].
`set_bit_array (varbit a, int b, int c, int[] d, int e) returns varbit`	Changes the values of the bits that are specified by a subscript array d to values b (0 or 1), and fills the bits that are beyond the original length with values c (0 or 1). The number of bits that are returned is e. For example, `set_bit_array('111100001111', 1, 0, array[4,5,6,7], 2)` returns 111111001111.
`set_bit_array_record (varbit a, int b, int c, int[] d) returns (varbit,int[])`	Changes the values of the bits that are specified by a subscript array d to values b (0 or 1), and fills the bits that are beyond the original length with values c (0 or 1). This function not only returns a bit string but also a subscript array. The subscript array indicates the positions of the bits whose values are changed. For example, `set_bit_array_record('111100001111', 0, 1, array[1,15])` returns 1011000011111110 and [1,15].
`set_bit_array_record (varbit a, int b, int c, int[] d, int e) returns (varbit,int[])`	Changes the values of the bits that are specified by a subscript array d to values b (0 or 1), and fills the bits that are beyond the original length with values c (0 or 1). This function not only returns a bit string but also a subscript array. The subscript array indicates the positions of the bits whose values are changed. This function returns results immediately after it changes values for a total of e bits. For example, `set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2)` returns 111111001111 and [4,5].
`bit_count_array (varbit a, int b, int[] c) returns int`	Counts the number of values b (0 or 1) among the bits that are specified by a subscript array c. For example, `bit_count_array('1111000011110000', 1, array[1,2,7,8])` returns 3.

Usage

Create the varbitx extension
```
CREATE EXTENSION varbitx;
```
Delete the varbitx extension
```
DROP EXTENSION varbitx;
```

Call the functions

You can execute the SELECT <function> statement to call the functions. Examples:

bit_count function

select bit_count('1111000011110000', 1, 5, 4);

Sample result:

 bit_count   
-----------  
         1  
(1 row)

set_bit_array_record function

select set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2);

Sample result:

  set_bit_array_record    
------------------------  
 (111111001111,"{4,5}")  
(1 row)

For more information about the functions and their descriptions, see Functions.