Re: ERROR: cannot cast type text to bit varying - Mailing list pgsql-general

On Thu, 2005-06-09 at 20:05 +0000, Matt Miller wrote:
> I'm trying to interpret strings of Y's and N's as bit vectors and
> perform bitwise ops on them.

Well, I ended up writing a bunch of code to accomplish what I initially
thought would be just some casting and bitops on built-in types.  I
really thought that the bit string types would help me, but I just
couldn't get them to work.

Here is my code in case I've reinvented the wheel and anyone wants to
make fun of me:

create or replace
FUNCTION flagset2num (flagset varchar) returns integer

AS $$

-- ========================================
-- interpret a flagset (a string of Y's and
-- N's) as a bit vector and convert it into
-- a number
-- ========================================

DECLARE
l_flagset varchar (32) := trim (flagset);
len integer := length (l_flagset);
ret_val integer;

BEGIN
ret_val := 0;
for i in reverse len .. 1 loop
    if (substr (l_flagset, i, 1) = 'Y') then
        ret_val := ret_val + power (2, len - i);
    end if;
end loop;

return ret_val;

END;

$$ language plpgsql;

create or replace
FUNCTION num2flagset (num integer, flagset_len integer)
returns varchar

AS $$

-- ========================================
-- interpret a decimal number as a bit
-- vector and convert it into a flagset
-- (a string of Y's and N's) of the
-- specified length
-- ========================================

DECLARE
ret_val varchar (16) := '';
hex_num varchar (4);
nibble varchar (4);

BEGIN
-- ----------------------------------------
-- use built-in to convert decimal number
-- to hex string, easing conversion to
-- binary
-- ----------------------------------------

hex_num := to_hex (num);

-- ----------------------------------------
-- convert hex string to binary string
-- using digit substitution
-- ----------------------------------------

for i in 1 .. length (hex_num) loop
    nibble := case (substr (hex_num, i, 1))
              when '0' then '0000'
              when '1' then '0001'
              when '2' then '0010'
              when '3' then '0011'
              when '4' then '0100'
              when '5' then '0101'
              when '6' then '0110'
              when '7' then '0111'
              when '8' then '1000'
              when '9' then '1001'
              when 'a' then '1010'
              when 'b' then '1011'
              when 'c' then '1100'
              when 'd' then '1101'
              when 'e' then '1110'
              when 'f' then '1111' end;
    ret_val := ret_val || nibble;
end loop;

-- ----------------------------------------
-- convert string of binary digits to
-- flagset
-- ----------------------------------------

return translate (ret_val, '01', 'NY');

END;

$$ language plpgsql;

create or replace
FUNCTION flagset_bitand (
flagset1 varchar,
flagset2 varchar
) returns varchar

AS $$

-- ========================================
-- perform bitwise "and" on flagsets,
-- returning a flagset
-- ========================================

BEGIN
return num2flagset (flagset2num (flagset1) & flagset2num (flagset2),
                    length (flagset1));

END;

$$ language plpgsql;

select flagset_bitand ('NYYN', 'NNYY');