Обсуждение: ERROR: cannot cast type text to bit varying

I'm trying to interpret strings of Y's and N's as bit vectors and
perform bitwise ops on them.  It's not working:

========beginning of output=============================
test=# select version ();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)

test=# select translate ('YNNY', 'YN', '10')::bit varying & translate
('NYYN', 'YN', '10')::bit varying;
ERROR:  cannot cast type text to bit varying
========end of output=============================

It's curious to me that the following is fine:

========beginning of output=============================
test=# select '1001'::bit varying;
 varbit
--------
 1001
(1 row)
========end of output=============================

As what data type is the literal '1001' being treated, that it can be
cast to bit varying, while text data (e.g. the result of "translate")
cannot be so cast?

> It's curious to me that the following is fine:
>
> ========beginning of output=============================
> test=# select '1001'::bit varying;
>  varbit
> --------
>  1001
> (1 row)
> ========end of output============================

Okay, I guess I'm not so curious, thanks to
http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SQL-
SYNTAX-TYPE-CASTS, which tells me:

"A cast applied to an unadorned string literal represents the initial
assignment of a type to a literal constant value, and so it will succeed
for any type"

But, I still have my initial problem:

> test=# select translate ('YNNY', 'YN', '10')::bit varying & translate
> ('NYYN', 'YN', '10')::bit varying;
> ERROR:  cannot cast type text to bit varying

Ideas?

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');

On Thu, Jun 09, 2005 at 10:18:27PM +0000, Matt Miller wrote:
> 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.

I imagine you could have done something involving textout() and
varbit_in(), like

alvherre=# select varbit_in(textout(translate('YYNY', 'YN', '10')), 123::oid, 32);
 varbit_in
-----------
 1101
(1 fila)

The OID parameter is unused, give it anything except NULL.  The integer
is the maximum length of the resulting varbit field.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La soledad es compañía"

> > 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 imagine you could have done something involving textout() and
> varbit_in(), like
>
> alvherre=# select varbit_in(textout(translate('YYNY', 'YN', '10')), 123::oid, 32);
>  varbit_in
> -----------
>  1101
> (1 fila)

Ah... This is more like it.  Thank you.

I'll explore this more tomorrow.  Are "varbit_in" and "textout"
documented?