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

Prerequisites

Your RDS instance runs one of the following PostgreSQL versions:
  • PostgreSQL 11
  • PostgreSQL 10

Functions supported

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.

Basic usage

  • Create the varbitx plug-in.
    CREATE EXTENSION varbitx;
  • Delete the varbitx plug-in.
    DROP EXTENSION varbitx;
  • Call the functions that are supported by the varbitx plug-in.

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

    • Execute the following statement to call the bit_count function:
      select bit_count('1111000011110000', 1, 5, 4);

      The following result is returned:

       bit_count   
-----------  
         1  
(1 row)
    • Execute the following statement to call the set_bit_array_record function:
      select set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2);

      The following result is returned:

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

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