[SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field

[TJ O'Donnell]

I use a c function, nbits_set that will do what you need.
I've posted the code in this email.

TJ O'Donnell
http://www.gnova.com

#include "postgres.h"
#include "utils/varbit.h"

Datum   nbits_set(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(nbits_set);
Datum
nbits_set(PG_FUNCTION_ARGS)
{
/* how many bits are set in a bitstring? */

        VarBit     *a = PG_GETARG_VARBIT_P(0);
        int n=0;
        int i;
        unsigned char *ap = VARBITS(a);
        unsigned char aval;
        for (i=0; i < VARBITBYTES(a); ++i) {
                aval = *ap; ++ap;
                if (aval == 0) continue;
                if (aval & 1) ++n;
                if (aval & 2) ++n;
                if (aval & 4) ++n;
                if (aval & 8) ++n;
                if (aval & 16) ++n;
                if (aval & 32) ++n;
                if (aval & 64) ++n;
                if (aval & 128) ++n;
        }
        PG_RETURN_INT32(n);
}

Hi all,

Am looking for a fast and efficient way to count the number of bits set (to 1) in a VARBIT field. I am currently using "LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS TEXT),'0','','g'))".

Allan.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql