Обсуждение: problem with bit(n) type

Hi, List!

kosten=# select version();
                                  version
---------------------------------------------------------------------------
 PostgreSQL 8.0.0rc5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)

kosten=# create table foo(t bit(4));
CREATE TABLE

kosten=# insert into foo values(B'0011');
INSERT 985996 1

kosten=# select t from foo;
  t
------
 0011
(1 row)

But if I do something like this:
kosten=# select t::text from foo;
ERROR:  cannot cast type bit to text

I get an error. Okay the system cannot  cast bit to text...
But this is the big problem for me... Is there a way to cast bit(n) to
text or varchar?

thanks in advance.

Sergey Karin

PS. I have found that there are some functions: varbit_in() and
varbit_out(). But they works with internal type cstring. And I cannot
cast cstring to varchar or text.

Binary data in bit(n) has no text equivalent, and so cannot be "cast" as text.
It has an "external representation", e.g. B'10010101', but this is not
the same as a cast.  In some cases, where there bit(n) has (n mod 8) = 0
and the bitstring happens to be valid ascii in the text range, one could
say there is a text equivalent, but in general not.

I think you're looking for some kind of printf-type %b function?  Maybe
someone has written one, or maybe you'll have to do so.  If you do,
how about a hex representation of bit(n) - I could use that ;)

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

But this is the big problem for me... Is there a way to cast bit(n) to
text or varchar?

thanks in advance.

Sergey Karin

On Wed, Oct 12, 2005 at 12:24:41PM +0400, Sergey Karin wrote:
> But if I do something like this:
> kosten=# select t::text from foo;
> ERROR:  cannot cast type bit to text
>
> I get an error. Okay the system cannot  cast bit to text...
> But this is the big problem for me... Is there a way to cast bit(n) to
> text or varchar?

You found the conversion functions, and you can use them to work around
this like follows:

test=# select textin(bit_out(b'1010'));
 textin
--------
 1010
(1 row)

So perhaps:

test=# create function bit_to_text (bit) returns text
as 'select textin(bit_out($1))' language sql strict immutable;
CREATE FUNCTION
test=# select substr( bit_to_text( b'10110001' ), 3, 3 );
 substr
--------
 110
(1 row)

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.