Обсуждение: Casting to varchar

Just discovered (the hard way) that casting a boolean column ::varchar
doesn't work. I assume I can add a function somewhere that will define a
default cast for this? Are there any other standard types that can't be cast
to varchar?

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On 04/05/2007 21:34, Scott Ribe wrote:

> Just discovered (the hard way) that casting a boolean column ::varchar
> doesn't work. I assume I can add a function somewhere that will define a
> default cast for this? Are there any other standard types that can't be cast

I just use something like this:

create or replace function bool2str(TheValue boolean)
returns varchar as
$$
begin
   if TheValue then
     return 'true';
   else
     return 'false';
   end if;
end;
$$
language plpgsql stable;

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Scott Ribe wrote:
> Just discovered (the hard way) that casting a boolean column ::varchar
> doesn't work. I assume I can add a function somewhere that will define a
> default cast for this?

Sure, see CREATE CAST.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Raymond O'Donnell wrote:
> On 04/05/2007 21:34, Scott Ribe wrote:
>
> >Just discovered (the hard way) that casting a boolean column ::varchar
> >doesn't work. I assume I can add a function somewhere that will define a
> >default cast for this? Are there any other standard types that can't be
> >cast
>
> I just use something like this:
>
> create or replace function bool2str(TheValue boolean)
> returns varchar as
> $$
> begin
>   if TheValue then
>     return 'true';
>   else
>     return 'false';
>   end if;
> end;
> $$
> language plpgsql stable;

To complete the example,

alvherre=# create cast (boolean as varchar) with function bool2str(bool);
CREATE CAST

alvherre=# select 't'::boolean::varchar;
 varchar
---------
 true
(1 fila)

Though I'd mark the function immutable rather than stable.

alvherre=# select 'f'::boolean::varchar;
 varchar
---------
 false
(1 fila)

alvherre=# select '0'::boolean::varchar;
 varchar
---------
 false
(1 fila)

alvherre=# select '123'::boolean::varchar;
ERROR:  invalid input syntax for type boolean: "123"


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

> Sure, see CREATE CAST.

Too simple ;-) I was expecting to have to dig into data type definitions...

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On May 4, 2007, at 15:34 , Scott Ribe wrote:

> Are there any other standard types that can't be cast
> to varchar?

You already got an answer to the first part of your question, but I
thought you might be interested in the second as well. Here's what I
did:

SELECT DISTINCT cast_from
FROM pg_cast c
NATURAL JOIN (
     SELECT oid as castsource, typname as cast_from
     FROM pg_type
     ) s
WHERE NOT EXISTS (
     SELECT 1
     FROM pg_cast i
     NATURAL JOIN (
         SELECT oid as casttarget, typname as cast_target
         FROM pg_type
         ) t
     WHERE cast_target = 'text'
         AND i.castsource = c.castsource
     )
ORDER BY cast_from;

   cast_from
--------------
abstime
bit
bool
box
circle
lseg
path
polygon
regclass
regoper
regoperator
regproc
regprocedure
regtype
reltime
text
varbit
(17 rows)

I don't know which of those you'd consider standard, but I believe
that's a complete list from HEAD of a few minutes ago.

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:
> On May 4, 2007, at 15:34 , Scott Ribe wrote:
>> Are there any other standard types that can't be cast
>> to varchar?

> You already got an answer to the first part of your question, but I
> thought you might be interested in the second as well.

Note that there's a proposal to allow explicit casts to text from any
type (by invoking the appropriate I/O function behind the scenes) and
I imagine we'd allow casts to varchar as well.  Not sure if this will
happen for 8.3, although it still could.

            regards, tom lane

> You already got an answer to the first part of your question, but I
> thought you might be interested in the second as well. Here's what I
> did:

Thanks. That's very helpful, to see a good example of using the built-in
catalog data.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice