extend PostgreSQL set_bit() set one bit to serial bits.

背景

PostgreSQL 支持bit或varbit类型.

通过set_bit函数可以设置比特变量的其中1个比特.

例如 :

digoal=# select set_bit('11111'::varbit, 1, 0);  
 set_bit   
---------  
 10111  
(1 row)  

以上把索引为1的比特位设置为0.

set_bit对应的C函数如下 :

src/backend/utils/adt/varbit.c

/*  
 * bitsetbit  
 *  
 * Given an instance of type 'bit' creates a new one with  
 * the Nth bit set to the given value.  
 *  
 * The bit location is specified left-to-right in a zero-based fashion  
 * consistent with the other get_bit and set_bit functions, but  
 * inconsistent with the standard substring, position, overlay functions  
 */  
Datum  
bitsetbit(PG_FUNCTION_ARGS)  
{  
        VarBit     *arg1 = PG_GETARG_VARBIT_P(0);  
        int32           n = PG_GETARG_INT32(1);  
        int32           newBit = PG_GETARG_INT32(2);  
        VarBit     *result;  
        int                     len,  
                                bitlen;  
        bits8      *r,  
                           *p;  
        int                     byteNo,  
                                bitNo;  
  
        bitlen = VARBITLEN(arg1);  
        if (n < 0 || n >= bitlen)  
                ereport(ERROR,  
                                (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),  
                                 errmsg("bit index %d out of valid range (0..%d)",  
                                                n, bitlen - 1)));  
  
        /*  
         * sanity check!  
         */  
        if (newBit != 0 && newBit != 1)  
                ereport(ERROR,  
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),  
                                 errmsg("new bit must be 0 or 1")));  
  
        len = VARSIZE(arg1);  
        result = (VarBit *) palloc(len);  
        SET_VARSIZE(result, len);  
        VARBITLEN(result) = bitlen;  
  
        p = VARBITS(arg1);  
        r = VARBITS(result);  
  
        memcpy(r, p, VARBITBYTES(arg1));  
  
        byteNo = n / BITS_PER_BYTE;  
        bitNo = BITS_PER_BYTE - 1 - (n % BITS_PER_BYTE);  
  
        /*  
         * Update the byte.  
         */  
        if (newBit == 0)  
                r[byteNo] &= (~(1 << bitNo));  
        else  
                r[byteNo] |= (1 << bitNo);  
  
        PG_RETURN_VARBIT_P(result);  
}  

如果要一次修改多个比特位, 可以对这个函数稍微修改一下, 放到PostgreSQL的lib库目录里面.

如下 :

vi bitsetvarbit.c  
#include "postgres.h"  
  
#include "access/htup.h"  
#include "libpq/pqformat.h"  
#include "nodes/nodeFuncs.h"  
#include "utils/array.h"  
#include "utils/varbit.h"  
  
PG_MODULE_MAGIC;  
  
PG_FUNCTION_INFO_V1(bitsetvarbit);  
  
Datum  
bitsetvarbit(PG_FUNCTION_ARGS)  
{  
        VarBit     *arg1 = PG_GETARG_VARBIT_P(0);  
        int32           n = PG_GETARG_INT32(1);  
        int32           cnt = PG_GETARG_INT32(2);  
        int32           newBit = PG_GETARG_INT32(3);  
        VarBit     *result;  
        int                     len,  
                                bitlen;  
        bits8      *r,  
                           *p;  
        int                     byteNo,  
                                bitNo,  
                                i;  
  
        bitlen = VARBITLEN(arg1);  
        if (n < 0 || n >= bitlen || (n+cnt) > bitlen)  
                ereport(ERROR,  
                                (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),  
                                 errmsg("bit index %d out of valid range (0..%d)",  
                                                n, bitlen-1)));  
        if (cnt <= 0)  
                ereport(ERROR,  
                                (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),  
                                 errmsg("can not set negative number of bits: %d",  
                                                cnt)));  
  
        /*  
         * sanity check!  
         */  
        if (newBit != 0 && newBit != 1)  
                ereport(ERROR,  
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),  
                                 errmsg("new bit must be 0 or 1")));  
  
        len = VARSIZE(arg1);  
        result = (VarBit *) palloc(len);  
        SET_VARSIZE(result, len);  
        VARBITLEN(result) = bitlen;  
  
        p = VARBITS(arg1);  
        r = VARBITS(result);  
  
        memcpy(r, p, VARBITBYTES(arg1));  
  
        i = 0;  
        while (cnt-- > 0)  
        {  
        byteNo = (n+i) / BITS_PER_BYTE;  
        bitNo = BITS_PER_BYTE - 1 - ((n+i) % BITS_PER_BYTE);  
  
        /*  
         * Update the byte.  
         */  
        if (newBit == 0)  
                r[byteNo] &= (~(1 << bitNo));  
        else  
                r[byteNo] |= (1 << bitNo);  
        i++;  
        }  
  
        PG_RETURN_VARBIT_P(result);  
}  

创建动态库, 拷贝到$PGHOME/lib目录 :

gcc -O3 -Wall -Wextra -Werror -I /home/ocz/postgresql-9.2.1/src/include -g -fPIC -c ./bitsetvarbit.c -o bitsetvarbit.o  
gcc -O3 -Wall -Wextra -Werror -I /home/ocz/postgresql-9.2.1/src/include -g -shared bitsetvarbit.o -o libbitsetvarbit.so  
cp libbitsetvarbit.so $PGHOME/lib/  

测试 :

ocz@db-172-16-3-150-> psql digoal postgres  
psql (9.2.1)  
Type "help" for help.  
digoal=# create or replace function bitsetvarbit(varbit,int,int,int) returns varbit as '$libdir/libbitsetvarbit.so', 'bitsetvarbit' language C STRICT;  
digoal=# select bitsetvarbit('11111'::varbit,1,2,0);  
 bitsetvarbit   
--------------  
 10011  
(1 row)  
digoal=# select bitsetvarbit('11111'::varbit,1,3,0);  
 bitsetvarbit   
--------------  
 10001  
(1 row)  
digoal=# select bitsetvarbit(repeat('0',10000)::varbit,9900,100,1);  
 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000  
01111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111  
(1 row)  

参考

1. 《get PostgreSQL’s next oid》

digoal’s 大量PostgreSQL文章入口