Обсуждение: Any functions to convert bit(5) to text?

Hi,
I use pg8 and ZeosLib 6.5.1. Zeos can't read fields type of array and bit,
so I need to convert these fields to text on the server side.

In case of arrays I can use array_to_string(), but I couldn't find any
functions to convert bit(*) to string (in a format like this: e.g. '10011').


Could anybody suggest me a built in function or I should write it myself?

Thank you,

-- Csaba

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 2005.02.07.

On Thu, Feb 10, 2005 at 07:32:35AM +0100, Együd Csaba wrote:
>
> In case of arrays I can use array_to_string(), but I couldn't find any
> functions to convert bit(*) to string (in a format like this: e.g. '10011').

I don't know if this will always work, but with current implementations
of PL/pgSQL you can do this:

CREATE FUNCTION bit2text(bit) RETURNS text AS '
BEGIN
    RETURN $1;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

See also CREATE CAST.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Thu, Feb 10, 2005 at 12:00:31AM -0700, Michael Fuhr wrote:
>
> CREATE FUNCTION bit2text(bit) RETURNS text AS '
> BEGIN
>     RETURN $1;
> END;
> ' LANGUAGE plpgsql IMMUTABLE STRICT;

This also appears to work:

CREATE TABLE foo (b varbit);
INSERT INTO foo VALUES ('10011');
SELECT textin(bit_out(b)) FROM foo;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Thu, Feb 10, 2005 at 12:16:53AM -0700, Michael Fuhr wrote:
>
> SELECT textin(bit_out(b)) FROM foo;

Is it generally true that you can convert between types using the
above method, assuming compatible syntax?  That is, using the source
type's output function to get a cstring that you pass to the target
type's input function?  Are there any potential problems with doing
this?  Is that how the PL/pgSQL RETURN trick works?  It seems a
handy way to cast one type to another when no built-in cast or
function exists.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi Michael,
at the first glance it seems to be perfect. In addition it works even for
arrays. Wow! :)

Thank you very much,
-- Csaba

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Thursday, February 10, 2005 8:32 AM
To: Együd Csaba
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Any functions to convert bit(5) to text?

On Thu, Feb 10, 2005 at 12:16:53AM -0700, Michael Fuhr wrote:
>
> SELECT textin(bit_out(b)) FROM foo;

Is it generally true that you can convert between types using the above
method, assuming compatible syntax?  That is, using the source type's output
function to get a cstring that you pass to the target type's input function?
Are there any potential problems with doing this?  Is that how the PL/pgSQL
RETURN trick works?  It seems a handy way to cast one type to another when
no built-in cast or function exists.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 2005.02.07.

Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Feb 10, 2005 at 12:16:53AM -0700, Michael Fuhr wrote:
>> SELECT textin(bit_out(b)) FROM foo;

> Is it generally true that you can convert between types using the
> above method, assuming compatible syntax?

IIRC, this has been reasonably safe since we made cstring into an actual
datatype, which was 7.3 or so.  Before that it was real easy to crash
the backend by trying to invoke I/O functions directly (mainly because
the old convention involving OPAQUE didn't afford any opportunity to do
type checking).

> Is that how the PL/pgSQL RETURN trick works?

plpgsql has always done this under-the-hood for assignment across
disparate data types.  I'd recommend using a plpgsql function rather
than messing with the I/O functions directly.  For one thing, you don't
have to look up the function names/signatures that way ;-)

            regards, tom lane