PostgreSQL MySQL 兼容性之 - bit 函数和操作符

bit 函数和操作符

MySQL

&
  Bitwise AND
<<
  Left shift
>>
  Shift right
BIT_COUNT
  Returns the number of set bits
^
  Bitwise XOR
|
  Bitwise OR
~
  Bitwise NOT

PostgreSQL

Operator	Description	Example	Result
||	concatenation	B'10001' || B'011'	10001011
&	bitwise AND	B'10001' & B'01101'	00001
|	bitwise OR	B'10001' | B'01101'	11101
#	bitwise XOR	B'10001' # B'01101'	11100
~	bitwise NOT	~ B'10001'	01110
<<	bitwise shift left	B'10001' << 3	01000
>>	bitwise shift right	B'10001' >> 2	00100

PostgreSQL bit_count 

需要自定义, 写两个C函数来解决

> vi bit_count.c
#include "postgres.h"
#include "fmgr.h"
#include "utils/varbit.h"
PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(bit_count1);
PG_FUNCTION_INFO_V1(bit_count2);

Datum
bit_count1(PG_FUNCTION_ARGS)
{

        VarBit     *arg = PG_GETARG_VARBIT_P(0);
        uint32          mask;
        bits8      *r;
        int                     nbits = 0;

        /* Check that the bit string is not too long */
        if (VARBITLEN(arg) > 32)
                ereport(ERROR,
                                (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                                 errmsg("integer out of range")));

        mask = 0;
        for (r = VARBITS(arg); r < VARBITEND(arg); r++)
        {
                mask <<= BITS_PER_BYTE;
                mask |= *r;
        }
        /* Now shift the mask to take account of the padding at the end */
        mask >>= VARBITPAD(arg);

        /* this code relies on mask being an unsigned type */
        while (mask)
        {
                if (mask & 1)
                        nbits++;
                mask >>= 1;
        }
        PG_RETURN_INT32(nbits);
}

Datum
bit_count2(PG_FUNCTION_ARGS)
{

        uint32          mask = PG_GETARG_INT32(0);
        int                     nbits = 0;

        while (mask)
        {
                if (mask & 1)
                        nbits++;
                mask >>= 1;
        }
        PG_RETURN_INT32(nbits);
}

> gcc -O3 -Wall -Wextra -Werror -I /home/digoal/postgresql-9.5.0/src/include -g -fPIC -c ./bit_count.c -o bit_count.o
> gcc -O3 -Wall -Wextra -Werror -I /home/digoal/postgresql-9.5.0/src/include -g -shared bit_count.o -o libbit_count.so
> cp libbit_count.so /home/digoal/pgsql9.5/lib/
> psql
postgres=# create or replace function bit_count(varbit) returns int as '$libdir/libbit_count.so', 'bit_count1' language c strict ;
CREATE FUNCTION
postgres=# create or replace function bit_count(int) returns int as '$libdir/libbit_count.so', 'bit_count2' language c strict ;
CREATE FUNCTION
postgres=# select bit_count(bit'1111');
 bit_count 
-----------
         4
(1 row)
postgres=# select bit_count(bit'1111011001');
 bit_count 
-----------
         7
(1 row)

postgres=# select bit_count(99);
 bit_count 
-----------
         4
(1 row)

postgres=# select bit_count(10);
 bit_count 
-----------
         2
(1 row)

PostgreSQL还支持set_bit

set_bit
postgres=#  select set_bit(bit'11111',1,0);
 set_bit 
---------
 10111
(1 row)
postgres=#  select set_bit(bit'11111',0,0);
 set_bit 
---------
 01111
(1 row)